Running macros in a defined range of sheets

Question

I am looking to run macros on different sheets defined by a specific range. In this case, I want to run the macros on the sheet that are defined between my sheets "A>>" and "<<Z". The sheets that are in that range have the same macro.

I tried using the following code but I'm probably missing call the macro on the active sheet and move on to the next sheet

Sub EvaluateAll()
    ' determine current bounds
    Dim StartIndex, EndIndex, LoopIndex As Integer
    StartIndex = Sheets("A>>").Index + 1
    EndIndex = Sheets("<<Z").Index - 1

    For LoopIndex = StartIndex To EndIndex
        ActiveSheet.Select
        'Call Macro1 in X Sheet'
    Next LoopIndex
End Sub

worksheets(loopindex).select, not that you need select anything probably. stackoverflow.com/questions/10714251/… — SJR
Not clear what you intend to do. At least, for me. ActiveSheet will always be the same, if your code will not activate another one. But in VBA you can do almost everything without selecting an object. In this case, certainly it is not need of activation. Should we understand that you have a "Macro1" Sub in all sheets you want running it? — FaneDuru

FaneDuru FaneDuru · Accepted Answer · 2020-11-26T12:27:07

Try the next code, please. You did not answer my clarification question, so the code assumes that there is a macro named "Macro1" in all sheets processed by the iteration:

Sub EvaluateAll()
    Dim StartIndex, EndIndex, LoopIndex As Integer
    StartIndex = Sheets("A>>").Index + 1
    EndIndex = Sheets("<<Z").Index - 1

    For LoopIndex = StartIndex To EndIndex
        Application.Run Sheets(LoopIndex).CodeName & ".Macro1"
    Next LoopIndex
End Sub

Running macros in a defined range of sheets

1 Answers