Macro will not run correctly if workbook's name is changed. What should I do?

[nevers711]

Hello everyone,

I created some codes that runs a macro. However, I noticed that if the users changes the Workbook's name, the macro will not run correctly. Below is partial of my codes. As you can see the name of the Excel file I have is "Annual Report.xlsm". What can I do to make it properly run no matter what workbook name is? Thank you!!

 Application.Run "Annual Report.xlsm'!Step1_Macro"

 

[Micron]

Best solution: hang the guilty by their eyelids and beat them until they blink. Make sure everyone else sees what happens when you mess around in network folders.

Really, this is not a coding problem and there's not much you can do in that regard. It's much bigger than that and I'd call it interference.

 

[rlv01]

Application.Run "Annual Report.xlsm'!Step1_Macro"

What is the name of the workbook that contains the above line of code? Is it also "Annual Report.xlsm"?

 

[nevers711]

Application.Run "Annual Report.xlsm'!Step1_Macro"

What is the name of the workbook that contains the above line of code? Is it also "Annual Report.xlsm"?

Hi rlv01, the workbook's name is "Annual Report.xlsm". I have several worksheets in this excel book though. Is this what you are asking? The worksheet's name is "Metrics", but I don't think it matters because the only error message I got is because somebody changed Annual Report.xlsm

 

[rlv01]

I mean that if everything is in "Annual Report.xlsm", then you should be able to replace

Application.Run "Annual Report.xlsm'!Step1_Macro"

with

Call Step1_Macro

or just

Step1_Macro

or

Application.Run "Step1_Macro"

or

Application.Run  ThisWorkbook.Name & "!Step1_Macro"

 

[nevers711]

Thank you!! It works perfectly!!