macro problem

[tandrea]

I have a master workbook that contains this macro. When I rename the workbook, the macro doesn't work because the name of the workbook changes. Is there something I can substitute in the macro instead of "Ovations Weekly Field Service Production Sheet Master.xls" that will work in a workbook no matter what it is named?

Thank you!


Application.Run "'Ovations Weekly Field Service Production Sheet Master.xls'!unprotect"
Windows("Ovations Weekly Field Service Production Sheet Master.xls").Activate
Application.Run "'Ovations Weekly Field Service Production Sheet Master.xls'!OvationsHospital"
Windows("Ovations Weekly Field Service Production Sheet Master.xls").Activate
Application.Run "'Ovations Weekly Field Service Production Sheet Master.xls'!protect"

 

[Damon Ostrander]

Hi tandrea,

Unless a macro resides in a different workbook than the calling code, the run command is not needed, in which case the following code should be equivalent to your code except that it refers to the macros in the ThisWorkbook object (NOT the macros in the ThisWorkbook event code module):

unprotect
'(probably is already active, but anyway...)
ThisWorkbook.Activate
OvationsHospital
'(re-activation only required if OvationsHospital changes the active workbook)
ThisWorkbook.Activate
protect