Macros don't work when file name changes

[Chuck Sena]

I created a workbook with many macros using the simple "record macro" tool.
This fiel was saved in *.XLS format. It is intended to be a master fille and
when you fill it out as a form you re-save it with a new name.

the problem I am running into is that after I re-save it wit a new name the
Macros won't work properly. I get an error message and when I click to run
the debugger the macro lists the original workbook name.

How do I fix this so that the macros work regardless of file name?

 

[Chip Pearson]

Without seeing the code, my guess is that the macro recorder put
explicit file names in the code, e.g., Windows("Book1.xls").Activate.
You need to modify the code so that it will reference the
ActiveWorkbook instead of a specific named workbook.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

 

[Dave Peterson]

It could depend on how you run the macro--or what object you assigned the macro
to so that you could run it.

On the other hand, it could depend on how the code is written.

If it's the first, then you'll have to share what you use to run the macro--a
shape placed on a worksheet or a button on a toolbar/QAT/Ribbon.

If it's a button on a worksheet, did you use the button from the Forms toolbar
or a Commandbutton from the control toolbox toolbar.

Does it run ok if you use Tools|Macro|macro|run (in xl2003 menus).

If it's the second, then maybe just rewriting the offending code to use the
activesheet would fix it.

But those are just guesses.

You'll get better answers if you supply more info--and if it's the code, you'll
get better answers if you share the code--and indicate what line caused the
trouble.