Linking Macros

[Diane]

Have workbook with 7 worksheets and run the same macro in each. Can I link
the worksheet macros into one master macro?

 

[Dave O]

Hi, Diane-
Do you mean you'd like to run the same macro on all seven tabs at
once? If yes, then you can modify the macro with a few extra lines of
code to do that.

Your current macro looks like this:
Sub YourMacroName()
{current code}
End Sub

If you'll modify it to read like this:
Sub YourMacroName()
dim Wksht
For Each Wksht In Sheets
If Wksht.Visible = True Then
Sheets(Wksht.Name).Select

{current code}

End If
Next Wksht
End Sub

This runs the same code on all visible worksheets. With a little more
code you can return the cursor to its original starting point (from
where you launched the macro).

Dave O
Eschew obfuscation

 

[Gary''s Student]

Sure. It depends on your goal. Say we have a macro that will run on the
active sheet and we want it to run on each worksheet in the workbook:

Sub slave()
Range("C1").Value = Range("A1").Value + Range("B1").Value
End Sub


Sub master()
For Each w In Worksheets
w.Activate
Call slave
Next
End Sub

The master goes to each sheet and runs the salve on t5hat sheet.

 

[Diane]

Dave,

Got it, but am a little new to the macro editing portion. Can you be a
little more literal in your direction. One of the tabs is called
Construction ~ can you write specifically the code to include this?
Diane

 

[Gord Dibben]

You mention "worksheet macros".

Do you mean worksheet event code which you have in each sheet's module?

If not, ignore rest of post and look at the other posts.

If, for example each sheet has the same Worksheet_Change code, you can place the
same code into Thisworkbook once and cover all sheets.

Private Sub Workbook_SheetChange _
(ByVal Sh As Object, ByVal Target As Range)

'your code here

End Sub


Gord Dibben MS Excel MVP