Calling a macro for each sheet in a loop

Will85

Board Regular
Joined
Apr 26, 2012
Messages
182
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.
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,169
Office Version
  1. 365
Platform
  1. Windows
Try
Code:
For Each Sht In Ary
   Sheets(CStr(Sht)).Select
   Call ABC123
Next Sht
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,817
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jul 23, 2007
Messages
4,676
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:

Mentor82

Active Member
Joined
Dec 30, 2018
Messages
307

ADVERTISEMENT

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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,169
Office Version
  1. 365
Platform
  1. Windows
You're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,108,768
Messages
5,524,787
Members
409,600
Latest member
Dunnowhatfor

This Week's Hot Topics

Top