Can't save macro

[LonnieP]

I've recorded a macro with the recorder and it runs correctly before I save the worksheet. After saving, I close the worksheet, then open it up again from disk. At this point I find the recorded Macro isn't in the file, and of course, doesn't work. I used the default name of Macro1, to be saved in "This worksheet". If I start to re-record the macro the default macro name iterates to Macro2.

What's happening. I've only got the one file "Loadings.xls" with no duplicates anywhere.

Help!

[shg]

Are you indeed using Excel 2003?

Where did you look for the macro?

If you do Alt+F8, the macro name does not appear?

[LonnieP]

Yep, it's 2003 and I used ALT-F8 to open the macro editor and the macro list is blank. When I open older sheets/workbooks and do the same thing to open the macro recorder, the macros are still there.

[shg]

And you are saving the workbook as an xls, not a csv, htm, ...?

[LonnieP]

Using "Save as" and saying yes to the overwrite prompt.

[LonnieP]

Just tried to save a simple worksheet with a simple macro with the name of Macro1, to be saved in "This worksheet". Saved, closed, reopened and the macro was gone. A re-record prompted new macro as Macro2.

[shg]

a simple macro with the name of Macro1, to be saved in "This worksheet"

ThisWorkbook

Just happens in one particular workbook?

[LonnieP]

No, any new workbook on this machine does it now. Just tried it on the secretaries machine and all worked as it should. Right now I'm thinking a re-install of Office may be in order. Is there some kind of global reset to factory defaults I can try first.

[shg]

Try Detect & Repair first.

[LonnieP]

Never heard of it. What and where is it?

[shg]

Help menu.

[LonnieP]

Well, Heck!

Just went back to the secretaries machine with the troubled file, opened it up, forced a blank row into the top of the screen, re-recorded the macro under a new name, made sure it was saved in "This workbook" saved the file, closed the file, opened the file, and all seems right with the world.

Kind of like fixing your car when you were a kid. Tear it down, don't replace anything, put it back together, and it's fixed now. Don't know what you did, but it works now.

Waiting for the other shoe to drop now. Keeping my fingers crossed.

Thanks for the help!

[royUK]

Were you saving your macro to PERSONAL.xls?

[LonnieP]

Roy, no, the macro has to be saved in "This workbook".

I'm building a modular worksheet suite with each module having five macros designed to manipulate each specific module. I've got thirty modules which would yield 150 macro names if all kept in one place. Too many to remember and disaster if you triggered the wrong one. Remember, no "undo's" for macro's, unless you save first. Hmmm, maybe save to a temp file first, a poor man's undo.

LonnieP

[royUK]

I said was the macro saved to PERONAL.xls, not that you should save it there

[LonnieP]

Sorry Roy, I guess I'm missing something here. When macros are saved to "This Workbook", doesn't that mean the currently open/active workbook, which in this case is "loadings.xls". I just assumed "Personal.xls" is keyed to the Log-in user and is one of the lookup locations for macros of unspecified file location. Kind of like the old DOS search path.

Your comment sounds like I got that wrong.

[royUK]

When you record a macro you choose the location. If you forgot to change it to Thisworkbook then it may have been saved in a new workbook which you didn't save or personal.xls

[LonnieP]

Could very well be what happened.

Thanks