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.

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.


"Old Fossil Bama" wrote:

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.

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


"Old Fossil Bama" wrote in message
ups.com...
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.

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.



Trevor Shuttleworth wrote:

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

"Old Fossil Bama" wrote in message
ups.com...
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.


--

Dave Peterson

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Old Fossil Bama" wrote in message
ups.com...
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.

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.

Dave Peterson wrote:

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.

Trevor Shuttleworth wrote:

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

"Old Fossil Bama" wrote in message
ups.com...
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.


--

Dave Peterson

--

Dave Peterson

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.

"Old Fossil Bama" wrote:

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.

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" wrote in message
...
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.



Trevor Shuttleworth wrote:

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

"Old Fossil Bama" wrote in message
ups.com...
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.


--

Dave Peterson

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



Trevor Shuttleworth wrote:

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" wrote in message
...
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.



Trevor Shuttleworth wrote:

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

"Old Fossil Bama" wrote in message
ups.com...
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.


--

Dave Peterson

--

Dave Peterson

On Aug 13, 3:54 pm, "Trevor Shuttleworth"
wrote:
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

"Old Fossil Bama" wrote in oglegroups.com...



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

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

Thanks.

I have checked out some previous postings, but theyallseem to apply
a single macro against multiple sheets, but I need to launch a macro
tied to each of 35 sheets.- Hide quoted text -

- 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.

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

Old Fossil Bama wrote:

On Aug 13, 3:54 pm, "Trevor Shuttleworth"
wrote:
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

"Old Fossil Bama" wrote in oglegroups.com...



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

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

Thanks.

I have checked out some previous postings, but theyallseem to apply
a single macro against multiple sheets, but I need to launch a macro
tied to each of 35 sheets.- Hide quoted text -

- 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

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


"Old Fossil Bama" wrote in message
oups.com...
On Aug 13, 3:54 pm, "Trevor Shuttleworth"
wrote:
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

"Old Fossil Bama" wrote in
oglegroups.com...



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

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

Thanks.

I have checked out some previous postings, but theyallseem to apply
a single macro against multiple sheets, but I need to launch a macro
tied to each of 35 sheets.- Hide quoted text -

- 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.