CODEWITHSUNDEEP
vbaexcel
Justin Beachley
Justin Beachley

Reputation: 3

Using a VBA copy sheet macro and formula r1c1

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):

Sub Macro4()
'
' Macro4 Macro
'
' Keyboard Shortcut: Ctrl+a
'
Sheets("<Null>").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("<Null>").Copy After:=Sheets(3)
ActiveCell.FormulaR1C1 = "='Dividing Walls Only'!RC[-2]"
Range("C4").Select
Sheets("<Null> (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("<Null> (2)").Copy After:=Sheets(4)
Range("C3").Select
ActiveCell.FormulaR1C1 = "='Dividing Walls Only'!R[1]C[-2]"
Range("C4").Select
Sheets("<Null> (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("<Null> (3)").Copy After:=Sheets(5)
Range("C3").Select
ActiveCell.FormulaR1C1 = "='Dividing Walls Only'!R[2]C[-2]"
Range("C4").Select
End Sub

Obviously, this would be silly to repeat 180 times. This is the Copy Sheet macro that I have already:

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

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.

Thanks!

Justin

Upvotes: 0

Views: 1519

Answers (2)

Shai Rado
Shai Rado

Reputation: 33672

From what I could understand from your post, the code below will run according to the number of times selected by the user in InputBox, and copy a Sheet at after the last on.

For each created Sheet it will add a Formula to Cell C4, I'm just not sure the logics in advancing the Formula for each sheet.

Sub CopySheets()

Dim x           As Long
Dim numtimes    As Long
Dim newSht      As Worksheet


x = Application.InputBox("Enter number of times to copy active sheet", Default:=1, Type:=1)

' optimize run time
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.DisplayAlerts = False

' create the Buttons on the original sheet
' (will be copied inside the loop for all other sheets)
ActiveSheet.Buttons.Add(541.5, 97.5, 95.25, 43.5).Select
ActiveSheet.Buttons.Add(541.5, 169.5, 94.5, 42.75).Select

For numtimes = 1 To x

    'Loop by using x as the index number to make x number copies
     ActiveWorkbook.ActiveSheet.Copy _
            After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)

    Set newSht = ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)

    ' give the new Sheet Name the reference of num of times
    newSht.Name = "<NULL " & numtimes & ">"

    ' advance the row number in the formula
    newSht.Range("C3").FormulaR1C1 = "='Dividing Walls Only'!R[" & numtimes & "1]C[-2]"

Next numtimes

' Resume Settings
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.DisplayAlerts = True

End Sub

Upvotes: 0

user3598756
user3598756

Reputation: 29421

may be this is what you are after:

Option Explicit

Sub CopySheet()
    Dim numtimes As Long, x As Long, rowIndex As Long

    Call OptimizeCode_Begin
    rowIndex = 4 '<-- this is the row index that will be used in the formula that'll be written in the first new sheet
    numtimes = Application.InputBox("Enter number of times to copy active sheet", Default:=1, Type:=1)
    For x = 1 To numtimes
        'Loop by using x as the index number to make x number copies
        ActiveWorkbook.ActiveSheet.Copy _
        After:=ActiveWorkbook.Sheets(3)
        Range("C3").Formula = "='Dividing Walls Only'!A" & rowIndex '<--| write formula in the new sheet cell "C3" referencing "Dividing Walls Only" worksheet column "A" cell in current 'rowIndex'
        rowIndex = rowIndex + 1 '<--| update row index for subsequent new sheet formula
    Next
    Call OptimizeCode_End
End Sub

you see I used Excel (i.e. Application) InputBox() method instead of VBA InputBox() one since the former lets you specify the return data type also (Type:=1 for numeric input), thus forcing the user input to the wanted one.

Upvotes: 0

Related Questions