title: VBA code - naming sheets according to items in a list, but also ensuring no sheet duplications
body: <p>I am perplexed. I have a workbook which is used as a template for parts of the business as a register. The users build a list of register items they are tracking. For each item in the main register I need to create a worksheet that provides more detail on the issue. The new sheets are copies of a template also in the workbook "TemplateCRA". The create action is done using a single macro when all entreis have been made or updated in the register sheet "Ownership"</p>
  
  <p>I started with this which works:</p>
  
  <pre><code>Sub Button1_Click()
  '
  ' Button1_Click Macro
  '
      Dim MyCell As Range, MyRange As Range
  
          Set MyRange = Sheets("Ownership").Range("B11:B30")
      Set MyRange = Range(MyRange, MyRange.End(xlDown))
  
      For Each MyCell In MyRange
          If IsEmpty(MyCell) Then End
          Sheets("TemplateCRA").Copy After:=Sheets(Sheets.Count) 'creates a new worksheet
          Sheets(Sheets.Count).Name = "CRA Ref " &amp; MyCell.Value ' renames the new worksheet
          Range("B6").Value = ActiveSheet.Name
  
      Next MyCell
  End Sub
  </code></pre>
  
  <p>Then I progressed to this in an attempt to ensure that the macro first checks that a sheet has not already been created for the registered item, and if so alerts the user, but then continues to cycle down the items list and creates and new sheets required.</p>
  
  <pre><code>Sub Button2_Click()
      '
      ' Button2_Click Macro
      '
      Dim MyCell As Range, MyRange As Range
      Dim sh As Worksheet, flg As Boolean
      Set MyRange = Sheets("Ownership").Range("B11:B30")
      Set MyRange = Range(MyRange, MyRange.End(xlDown))
      For Each MyCell In MyRange
              If IsEmpty(MyCell) Then End
  
              For Each sh In Worksheets
                  If sh.Name Like "CRA Ref " &amp; MyCell.Value Then flg = True: Exit For
              Next
              If flg = True Then
                  MsgBox sh.Name &amp; " Found!"
              ElseIf flg = False Then
                  MsgBox "Creating CRA Ref " &amp; MyCell.Value &amp; " now!"
                  Sheets("TemplateCRA").Copy After:=Sheets(Sheets.Count) 'creates a new worksheet
                  Sheets(Sheets.Count).Name = "CRA Ref " &amp; MyCell.Value ' renames the new worksheet
                  Range("B6").Value = ActiveSheet.Name
              End If
  
      Next MyCell
      MsgBox "You may now complete your CRA for each item"
  End Sub
  </code></pre>
  
  <p>But this doesnt work properly. What appears to happen is that :</p>
  
  <p>the sh.Name check cycles through OK reporting that sheets are found until it finds an item without a sheet reports a 
  <code>Run Time error 91 - object variable with block variable not set</code> 
  in the first MsgBox line.</p>
  
  <p>Can anyone suggest what I have wrong?</p>
  
  <p>Cheers</p>