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??
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

btadams

Well-known Member
Joined
Jan 6, 2003
Messages
1,942
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,942
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,084
Messages
5,857,269
Members
431,867
Latest member
Dalorian

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