Calling a macro for each sheet in a loop

Will85

Board Regular
Joined
Apr 26, 2012
Messages
240
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Try
Code:
For Each Sht In Ary
   Sheets(CStr(Sht)).Select
   Call ABC123
Next Sht
 
Upvote 0
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.
 
Upvote 0
Hi Will85,

Try this:

Code:
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
 
Last edited:
Upvote 0
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.
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top