Macro doesn't work when workbook is renamed

**tandi** · 07-23-2012, 11:45 AM

I have a worksheet that has a button, which runs a macro that copies certain information into another workbook. The macro is saved in the workbook, because it will be used by 5 different people. When the workbook is opened it is renamed so the orginal stays empty for the next person. But, after it is renamed, the macro will not work. Is there a way to make the macro work no matter what the file name is?
I am using Excel 2010

**abousetta** · 07-23-2012, 11:51 AM

We'll need to see the code. It's probably a simple tweak but can't do it blindly.

**tandi** · 07-23-2012, 12:05 PM

Please Login or Register  to view this content.

**abousetta** · 07-23-2012, 12:19 PM

Please use code tags around your code as it makes it easier to read.

I am thinking that the code is in a workbook named TEST - QUOTE SHEET.xlsm. If so, change all the lines with:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Now having said that, this code should be cleaned up. You don't need to activate workbooks back and forth. If this is of interest to you, then you can start a new thread asking for help on cleaning up the code and making it more efficient.

**tandi** · 07-23-2012, 12:49 PM

Thank you, I will make the change and see if that corrects my problem. I'm sorry, but I don't know what code tags are. I am self taught and know just enough to be dangerous. I appreciate your help and I will submit another thread to clean up the code. Thanks a million.

**abousetta** · 07-23-2012, 01:13 PM

Code tags are available on the 'Go Advanced' pane or you can manually put them in by putting [CODE] ... [/CODE?] around your code. (just remove the question mark I put in to prevent it from recognizing the Code tags)

If you your problem is solved then please mark the thread as Solved (under Thread Tools or Advanced Pane >> First post >> change the prefix).

Good luck.

abousetta