Macro not working after change in file name?

[jm_dude]

Hello All,

I recently created a system of macros (with the greatly appreciated help from this very board). Is there a way to apply the already-created macro to different excel files without the original file open?? I tried taking the original excel file which i used to create the macros and "saved as" a different file name and sure enough the macros didnt work (as I received a Debug error, alerting me the original file could not be found) but when I have the original file open along with the newly saved-as file the macro works... Any suggestions??

 

[btadams]

There could be many reasons for this. My first thought is to look for any references in your code to the workbook that contains the code. For example if you have a line of code such as

Workbooks("My Macro Workbook.xls").Close

change it to

ThisWorkbook.Close

 

[jm_dude]

how would I change something like this:

Application.Run "'WorkbookXYZ.xls'!Macro24"
ActiveCell.Offset(0, -6).Range("A1").Select

Any ideas??

 

[btadams]

Sounds like what you want to do is save the file with the macros as an Add-in (*.xla) and distribute it to your users that way.

Does WorkbookXYZ.xls contain all your macros?

 

[Smitty]

Welcome to the Board!

Is there a way to apply the already-created macro to different excel files without the original file open?

You can store reusable code in Personal.xls, which makes code available to all open workbooks.

If you don't already have one, with a blank wb open record a new macro and in the "Store Macro in" dialog select Personal Macro Workbook. Then quit recording. The VBE will have created Personal.xls for you.

You can then copy all of your reusable code into that. Just note that you will then need to remove any specific references to other workbooks in your calling code.

You'll also want to record a new macro and reset the storage location to the active wb, not Personal.xls.

Hope that helps!