Rename copied sheets based on list that skips cells

[TrafficEG]

Hi, I have a workbook that has two tabs to start with "Input" and "Template". I have a macro already developed that will copy "Template" based on a value I input but I need those copied sheets to be renamed based on a value that appears in A13, A23, A33, etc.

 

[Joe4]

Where exactly do these values appear?
Is there some range you want it to loop through?

 

[TrafficEG]

My plan is that you will run the first macro, which copies the "Template" sheet based on a number you input. Then you would run a second macro that will rename the sheets made from the first macro with values that are in the "Input" sheet starting at cell A13 and every 10 cells below that (ie cell A23, A33, etc) until it runs out of sheets to rename.

 

[Joe4]

Why not just have one macro that starts in cell A13, and copies the template, renames it, and then moves down 10 row to A23, etc until the end of the data in column A on the Input sheet?

 

[TrafficEG]

I could not get a macro to do that and work . I am new to VBA coding.

 

[Joe4]

Give this a try:

Sub MyInsertNewSheets()

    Dim wsTmp As Worksheet
    Dim wsInp As Worksheet
    Dim wsNew As Worksheet
    Dim r As Long
    Dim lr As Long

    Application.ScreenUpdating = False

'   Set worksheet variables
    Set wsTmp = Sheets("Template")
    Set wsInp = Sheets("Input")

'   Find last row with data in column A on Input sheet
    lr = wsInp.Cells(Rows.Count, "A").End(xlUp).Row

'   Loop through column A on Input sheet, starting in A13 jumping 10 rows at a time
    For r = 13 To lr Step 10
'       Add new sheet after last sheet
        Sheets.Add After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count)
'       Capture new sheet
        Set wsNew = ActiveSheet
'       Copy template to new sheet
        wsTmp.Cells.Copy
        wsNew.Activate
        ActiveSheet.Paste
        Application.CutCopyMode = False
'       Rename new sheet
        wsNew.Name = wsInp.Cells(r, "A")
    Next r

    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!"
    
End Sub

 

[TrafficEG]

that works beautifully, thank you!

 

[Joe4]

You are welcome.
Glad I was able to help!