Macro not working after change in file name?

jm_dude

New Member
Joined
Jun 3, 2008
Messages
9
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??
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

btadams

Well-known Member
Joined
Jan 6, 2003
Messages
1,941
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

New Member
Joined
Jun 3, 2008
Messages
9
how would I change something like this:

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

Any ideas??
 

btadams

Well-known Member
Joined
Jan 6, 2003
Messages
1,941
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

Legend
Joined
May 15, 2003
Messages
29,536
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,601
Messages
5,597,117
Members
414,126
Latest member
jellevansoelen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top