Using a VBA copy sheet macro and formula r1c1
<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>