run all worksheet macros

[Old Fossil Bama]

I have a workbook with 35 worksheets, each with a different macro.

How can I create a workbook level macro to run the specific macro tied
to each of the 35 worksheets?

Thanks.

I have checked out some previous postings, but they all seem to apply
a single macro against multiple sheets, but I need to launch a macro
tied to each of 35 sheets.

 

[Guest]

First create a list of the Macros you have attach the list to a dropdown box,
and set the on change event to execute a Case statement, on each case you
will have a call for the corresponding macro to be executed.

Michael Arch.

 

[Trevor Shuttleworth]

One way, but a little tedious:

Sub CallSheetMacros()
' assumes macro is called Macro1 in each sheet
Sheet1.Macro1
Sheet2.Macro1
Sheet3.Macro1
:
:
Sheet3.Macro1
End Sub

Sub test()
' more scalable ... still assumes macro is called Macro1 in each sheet
For Each sht In Sheets
On Error Resume Next
sht.Macro1
On Error GoTo 0
Next
End Sub

Regards

Trevor

 

[Dave Peterson]

I think that the second suggestion has to be more like:

Sub test()
' more scalable ... still assumes macro is called Macro1 in each sheet
For Each sht In Sheets
On Error Resume Next
application.run sht.codename & ".Macro1"
On Error GoTo 0
Next
End Sub

The error checking may not be required if all the worksheet modules have a
procedure named Macro1.

 

[Don Guillett]

Or, you should be able to use one macro for all sheets unless there is a lot
of difference for each sheet.
Examples?

 

[Dave Peterson]

In a different thread JE McGimpsey showed that this was possible using code
similar to what Trevor showed.

Option Explicit
Sub aa()
Dim sht As Variant 'or As Object
For Each sht In Worksheets
sht.dtest
Next sht
End Sub

It didn't work for me the way Trevor wrote it because I used:
dim sht as worksheet

If I had left sht as an undeclared variant (or declared it as an object or a
variant), it would have worked fine.

Sorry for doubting your code Trevor <bg>.

 

[Guest]

Based on this statement:

I have checked out some previous postings, but they all seem to apply
a single macro against multiple sheets, but I need to launch a macro
tied to each of 35 sheets.

I deduce that you have thirty five different macros, even though they may
perform similar operations, one macro cannot be run against all thirty five
sheets. That means that you would have to use a master macro like Trevor
suggested where you call each macro in sequence. Watch the syntax.

 

[Trevor Shuttleworth]

Why do you say "I think that the second suggestion has to be more like:".

I tested the code I provided and it works as presented. What issues do you
envisage ?

Regards

Trevor

 

[Dave Peterson]

I used:
dim sht as worksheet
and the code failed.

 

[Old Fossil Bama]

One way, but a little tedious:

Sub CallSheetMacros()
' assumes macro is called Macro1 in each sheet
Sheet1.Macro1
Sheet2.Macro1
Sheet3.Macro1
:
:
Sheet3.Macro1
End Sub

Sub test()
' more scalable ... still assumes macro is called Macro1 in each sheet
For Each sht In Sheets
On Error Resume Next
sht.Macro1
On Error GoTo 0
Next
End Sub

Regards

Trevor






- Show quoted text -

Thanks, Trevor.

Tried the code like so:

Sub CallSheetMacros()
Sheet1.main
Sheet2.main
Sheet3.main
.........etc
End Sub

The problem is that as each macro was called, the open worksheet focus
remained on Sheet1, resulting in all the succeeding macros being run
against it instead of the corresponding worksheet.

How can I shift focus to the correct sheet each time before the macro
runs?

Something like this:

set focus to Sheet 1
Sheet1.main
set focus to Sheet 2
Sheet2.main
set focus to Sheet 3
Sheet3.main
etc.....

Thanks again.

 

[Dave Peterson]

sheet1.select
sheet1.main
sheet2.select
sheet2.main

 

[Trevor Shuttleworth]

Aaah ... if the macro name *is* the same on every sheet, I'd be inclined to
go for:

Sub test()
' more scalable ... still assumes macro is called Macro1 in each sheet
Dim sht As Variant
Application.ScreenUpdating = False
For Each sht In Sheets
On Error Resume Next
With sht
.Select
.macro1
End With
On Error GoTo 0
Next
Application.ScreenUpdating = True
End Sub

Saves the repetition

Regards

Trevor