VBA Apllication.run and macros when spreadsheet name changes

[Guest]

How can change the following statement to run a macro so I do not have to
change the spreadsheet name every time it changes.

Application.Run "'Salesman pay 2.9.xls'!Midmonthnumbersmove"

'Salesman pay 2.9.xls' is the spreadsheet name in this example. I would
just like the statement to run the macro Midmonthnumbersmove no matter what
the spreadsheet name is.

 

[Gary Keramidas]

if this is the active workbook, you don't need the spreadsheet name. is this
in some other workbook?

 

[Nigel]

You can call a macro from an open workbook using....

Call Midmonthnumbersmove

 

[Bob Phillips]

If it is always referring to the activeworkbook, you could use

Application.Run "'" & Activeworkbook.Name & "'!Midmonthnumbersmove"


but better would be to set a object variable to the book when you open it,
and use that

Set oWB = Activewrokbook

...

Application.Run "'" & oWB.Name & "'!Midmonthnumbersmove"

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Works great! thank you very much for your help!

 

[Rody Meulman]

Bob,

Why is your way better? What if the workbook was saved with an other name
after opening?

Please explain.

Grtz,

Rody