Excel 2007 : Macros in Original File Changing to New (Copied) Book Name

[Dustin]

Greetings,

I have a “tool” that allows a user to select data by marketplace and sales basis (combo boxes), interact with it (add data) then copy that worksheet to a new workbook that turns the various formulas to values. It’s a fairly simple macro.

The sheet in the original tool has several other simple macros as well (“Back” to a home page, “Clear” etc.). These are also cleared out when the new book is created. However, when going back to the original file, all the buttons on it now look for their assigned macros in the NEW book (whether or not that book has been saved, closed, or what have you)! Of course, since they are not there, an error message pops up stating that the macro is not available.

All of the macros were originally saved to “This Workbook,” but then I even tried assigning them to the specific file name (let’s call it “Tool.xlsm”). But even then, the macros all still try to run from the new book.

Note that this does not happen at all in Excel 2003; the macros remain fixed to the original file. I can make as many new books as I please; the macros in “Tool.xls” continue to work with no problems. Unfortunately about half my users have migrated to 2007.

To be clear, I built the tool in 2003. I tested it in 2007 and adjusted everything as needed. Everything works in 2007. The only issue is this one quirky thing that I cannot figure out. Clearly it’s beyond my pay grade.

I hope this all makes sense.

Help!

Thanks!
Dustin

[Dustin]

Since the data in the file is proprietary, I couldn’t attach it. But I went through and tweaked it so the details are lost, but the “data” is there and the thing functions. Hopefully someone (he says fingers crossed) can download it and duplicate my dilemma (and more importantly) tell me the heck I am doing wrong! (Note that the copied sheet will have "#VALUE!" errors in it; that's because the OFFSET function is going whacky in the download. My concern is the macro issue.)

Many thanks in advance!
Dustin