title: Using a VBA copy sheet macro and formula r1c1
body: <p>Maybe I've been staring at this for too long, but I have a macro that copies worksheets in Excel that works.  What I'm also trying to do is include this into the loop (just the R1C1 formula from this recorded macro):</p>
  
  <pre><code>Sub Macro4()
  '
  ' Macro4 Macro
  '
  ' Keyboard Shortcut: Ctrl+a
  '
  Sheets("&lt;Null&gt;").Select
  ActiveSheet.Buttons.Add(541.5, 97.5, 95.25, 43.5).Select
  ActiveSheet.Buttons.Add(541.5, 169.5, 94.5, 42.75).Select
  Sheets("&lt;Null&gt;").Copy After:=Sheets(3)
  ActiveCell.FormulaR1C1 = "='Dividing Walls Only'!RC[-2]"
  Range("C4").Select
  Sheets("&lt;Null&gt; (2)").Select
  ActiveSheet.Buttons.Add(541.5, 97.5, 95.25, 43.5).Select
  ActiveSheet.Buttons.Add(541.5, 169.5, 95.25, 42.75).Select
  Sheets("&lt;Null&gt; (2)").Copy After:=Sheets(4)
  Range("C3").Select
  ActiveCell.FormulaR1C1 = "='Dividing Walls Only'!R[1]C[-2]"
  Range("C4").Select
  Sheets("&lt;Null&gt; (3)").Select
  ActiveSheet.Buttons.Add(541.5, 97.5, 95.25, 43.5).Select
  ActiveSheet.Buttons.Add(541.5, 169.5, 95.25, 42.75).Select
  Sheets("&lt;Null&gt; (3)").Copy After:=Sheets(5)
  Range("C3").Select
  ActiveCell.FormulaR1C1 = "='Dividing Walls Only'!R[2]C[-2]"
  Range("C4").Select
  End Sub
  </code></pre>
  
  <p>Obviously, this would be silly to repeat 180 times.  This is the Copy Sheet macro that I have already:</p>
  
  <pre><code>Sub CopySheet()
  
  Call OptimizeCode_Begin
  
  Dim x As Integer
  
  x = InputBox("Enter number of times to copy active sheet")
  For numtimes = 1 To x
     'Loop by using x as the index number to make x number copies
     ActiveWorkbook.ActiveSheet.Copy _
        After:=ActiveWorkbook.Sheets(3)
        'Put copies in front of Sheet3
        'Might need to move the new sheets
  
  Next
  
  Call OptimizeCode_End
  
  End Sub
  </code></pre>
  
  <p>What I would like to do is either incorporate a nested loop or something to automatically advance the R1C1 formula for each sheet that would keep me from having to type in the cell I'm trying to reference after all the sheets have copied.  Any help would be appreciated.</p>
  
  <p>Thanks!</p>
  
  <p>Justin</p>