title: Copying and renaming a template worksheet depending on a range of cell values on a loop
body: <p>I have a template sheet that I have set up named "Template".</p>
  
  <p>I have a range of cells on another worksheet called "Formulation" that I would like it to look through the range "G7:W7" and create a copy of the "Template" and rename it accordingly.</p>
  
  <p>I have adapted a piece of code I have found but I keep encountering a run time error 13 - type mismatch.</p>
  
  <p>Here is the code:</p>
  
  <pre><code>`Sub CopyInfoSheetandInsert()
  '
  ' CopyInfoSheetandInsert Macro
  '
  Dim rcell As Range
  Dim Background As Worksheet
  Set Background = Sheets("Formulation")
  
  For Each rcell In Range("D7:W7")
  
      If rcell.Value &lt;&gt; "" Then
  
            Sheets("Template").Copy Before:=Sheets("COSHH")
            Sheets("Template (2)").Name = rcell.Value
  
      End If
  
  Next rcell
  
  
  
  End Sub
  </code></pre>
  
  <p>Any advice would be greatly appreciated!</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>By moving the macro button to the formulation page the copy function now works however, on the following line of code I now get a subscript out of range error?</p>
  
  <pre><code>Sheets("Template(2)").Name = rcell.Value
  </code></pre>
  
  <p>Kind Regards,</p>
  
  <p>Aidan</p>