Calling a macro for each sheet in a loop

[Will85]

Hello,

I am struggling with trying to get my macro to go to each individual sheet and call (run) another macro

Dim ary As Variant
Dim sht As Variant

With Sheets("SheetsToRun")
ary = .Range("B1", .Range("B" & Rows.Count).End(xlUp)).Value
End With
For Each sht In ary
With Sheets(CStr(sht))
Call ABC123
End With
Next sht

On the tab "SheetsToRun" cells B1:B5 I have tab names in my workbook

When I run the macro, it successfully runs my ABC123 on the first tab (value in SheetsToRun cell B1), but it does not go to the next tab (values in SheetsToRun cells B2:B5)

Instead it repeats on the first tab over and over.

 

[Fluff]

Try

For Each Sht In Ary
   Sheets(CStr(Sht)).Select
   Call ABC123
Next Sht

 

[Norie]

The With statement in the code doesn't do anything.

If you want the code in ABC123 to run on each sheet named in B1:B5 you either need to activate each sheet before you call ABC123 or change ABC123 to take the sheet as an argument and refer to the passed sheet in the code.

 

[Trebor76]

Hi Will85,

Try this:

Option Explicit
Sub Macro1()

    Dim rngMyCell As Range
    
    Application.ScreenUpdating = False
    
    For Each rngMyCell In Sheets("SheetsToRun").Range("B1", Sheets("SheetsToRun").Range("B" & Rows.Count).End(xlUp))
        Sheets(CStr(rngMyCell.Value)).Select
        Call ABC123
    Next rngMyCell
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[Mentor82]

Hello,

I am struggling with trying to get my macro to go to each individual sheet and call (run) another macro

Dim ary As Variant
Dim sht As Variant

With Sheets("SheetsToRun")
ary = .Range("B1", .Range("B" & Rows.Count).End(xlUp)).Value
End With
For Each sht In ary
With Sheets(CStr(sht))
Call ABC123
End With
Next sht

On the tab "SheetsToRun" cells B1:B5 I have tab names in my workbook

When I run the macro, it successfully runs my ABC123 on the first tab (value in SheetsToRun cell B1), but it does not go to the next tab (values in SheetsToRun cells B2:B5)

Instead it repeats on the first tab over and over.

Hi,
Try this. I do not know how your macro ABC123 is developed, therefore I activate each ws from the ary found in your workbook.

Sub CallMacroInWs()
Dim ary As Variant
Dim sht as worksheet
Dim i&

With Sheets("SheetsToRun")
ary = .Range("B1", .Range("B" & Rows.Count).End(xlUp)).Value
End With

For i=1 to ubound(ary)
       For each sht in worksheets
              If sht.name = ary(i)
                    Sht.activate
                    Call ABC123
                    Exit for
              End if
       Next each
Next i

End sub

 

[Will85]

see response below

 

[Will85]

Try

For Each Sht In Ary
   Sheets(CStr(Sht)).Select
   Call ABC123
Next Sht

Thank you!

 

[Fluff]

You're welcome