Dynamic Filenames with Application.Run
 

I have an Excel file containing multiple worksheets and multiple
macros.

For reporting purposes, the Excel filename contains a date (for
example, "Measures - 10-05-05 v1.xls"

One of the macros calls another macro within the file. The way the
file is setup, I need to keep the macros on seperate worksheets.

The problem I have is that I must edit the macro every time I rename /
resave the file. Anytime the file is saved with the new date, the macro
doesn't work since the filename is included (ie. Application.Run
"'Measures - 10-05-05 v1.xls'!Macro_1")

Is there a way to use a dynamic filename in the Application.Run
statement such that I don't have to change the macro everytime I rename
the file?

Dynamic Filenames with Application.Run
 

to call a macro within the same workbook you should only have to do

Macro1

to run/call it

You should have no need to use Run or the workbook name.

However, you can get the workbook name with

ThisWorkbook.Name

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
I have an Excel file containing multiple worksheets and multiple
macros.

For reporting purposes, the Excel filename contains a date (for
example, "Measures - 10-05-05 v1.xls"

One of the macros calls another macro within the file. The way the
file is setup, I need to keep the macros on seperate worksheets.

The problem I have is that I must edit the macro every time I rename /
resave the file. Anytime the file is saved with the new date, the macro
doesn't work since the filename is included (ie. Application.Run
"'Measures - 10-05-05 v1.xls'!Macro_1")

Is there a way to use a dynamic filename in the Application.Run
statement such that I don't have to change the macro everytime I rename
the file?