Run Macro on Each Sheet / Next 29 sheets

[Keep It Simple Stupid]

I have a tab that lists my desired sheet names in Column A, rows 1-29. (There are exactly 29 rows already created).

Dim i As Long
For i = 1 To ActiveWorkbook.Sheets.Count
On Error Resume Next
ActiveWorkbook.Sheets(i).Name = ActiveSheet.Cells(i, "A").Text
Next i

I've realized now that I need to run another macro within each of these new sheets (and only these new sheets) to insert some formulas, etc. Where can I put the macro information so it works on every sheet?

 

[buxtongt]

It looks like any action you want to happen on each sheet would need to be directly above the "Next i" line, so that it is executed after the sheet is selected, but before you move to the next one. Hope this helps.

 

[Keep It Simple Stupid]

I tried that - it ends up running the macro on the sheet that lists the sheet names in column A

 

[Keep It Simple Stupid]

Does anyone else have any ideas? I am still looking for a solution....

 

[shg]

One way:

Sub KISS()
    Dim i           As Long
    Dim wks         As Worksheet
    
    For i = 1 To ActiveWorkbook.Sheets.Count
        Set wks = ActiveWorkbook.Sheets(i)
        With wks
            .Name = ActiveSheet.Cells(i, "A").Text
            
            ' carry on; e.g.,
            .Range("A1").Value = "Bob"
            '...
        End With
    Next i
End Sub

 

[Keep It Simple Stupid]

Brilliant! Thank you so much!

 

[shg]

You're welcome, glad it helped.