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. https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — SJR, Nov 26 '20 at 12:04
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, Nov 26 '20 at 12:16

score 1 · Accepted Answer · answered Nov 26 '20 at 12:27

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 Answers1