VBA - Run macros over all sheets, not referring to workbook name

tlc53

Active Member
Joined
Jul 26, 2018
Messages
365
Hi,
I've finally got everything working how I need it to (thanks to the people on here!) and now I'm at the last hurdle.
From my Dashboard sheet, I then want to select each sheet and run the necessary macro. Once done, it takes me back to the Dashboard.
The problem with my code below, is that if refers to Workbook name 'Invoice Converter - Template'. I really need it not to, as the sheet will be renamed each month and this will stop the macro working.
How do I get around this/run the macros on each sheet? Thank you.

Code:
Sub ApplyAll()
'
' ApplyAll Macro
'


'
   Sheets("Monthly Data").Select
    Application.Run "'Invoice Converter - Template.xlsm'!CleanUp"
    Sheets("Cordis").Select
    Application.Run "'Invoice Converter - Template.xlsm'!ClientNarrative"
    Sheets("Hilton Banquets").Select
    Application.Run "'Invoice Converter - Template.xlsm'!ClientNarrative"
    Sheets("Hilton Cluster").Select
    Application.Run "'Invoice Converter - Template.xlsm'!ClientNarrative"
    Sheets("FourPoints").Select
    Application.Run "'Invoice Converter - Template.xlsm'!ClientNarrative"
    Sheets("Hilton Guest").Select
    Application.Run "'Invoice Converter - Template.xlsm'!ClientNarrative"
    Sheets("Pullman").Select
    Application.Run "'Invoice Converter - Template.xlsm'!ClientNarrative"
    Sheets("Sebel Manukau").Select
    Application.Run "'Invoice Converter - Template.xlsm'!ClientNarrative"
    Sheets("Stamford Plaza").Select
    Application.Run "'Invoice Converter - Template.xlsm'!ClientNarrative"
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    Sheets("Dashboard").Select
    CommandButton1.Enabled = False
End Sub
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Mentor82

Board Regular
Joined
Dec 30, 2018
Messages
203
Hi,
Is the workbook Template.xlsm, where you have the macro in place, the same from which you run it or another one? If that's the same then get a rid of all application run staff living after each select only the name of the macro you want to run. Check below code.

Code:
Sub ApplyAll()
Sheets("Monthly Data").Select
CleanUp
Sheets("Cordis").Select
ClientNarrative
Sheets("Hilton Banquets").Select
ClientNarrative
Sheets("Hilton Cluster").Select
ClientNarrative
Sheets("FourPoints").Select
ClientNarrative
Sheets("Hilton Guest").Select
ClientNarrative
Sheets("Pullman").Select
ClientNarrative
Sheets("Sebel Manukau").Select
ClientNarrative
Sheets("Stamford Plaza").Select
ClientNarrative
Sheets("Dashboard").Select
CommandButton1.Enabled = False
End sub
 

Watch MrExcel Video

Forum statistics

Threads
1,090,047
Messages
5,412,050
Members
403,409
Latest member
IHRAcer

This Week's Hot Topics

Top