Macros going dead!! ArGH!

[Lord Eck]

This has been bugging me BIG TIME,Im BOILING right now over this! Its happened twice, with tons of macros I spent HOURS making! OK, I have a workbook with several sheets, and several macros, call it "workbook1". Let's say I want to copy one sheet of this workbook to a new workbook, to send as an example to someone. Easy enough, right? Ok, so I "copy sheet" to new workbook, we'll call it "workbook2". I send "workbook 2" to a friend, and delete my copy of "workbook2" off my system. I dont expect my macros to work in his copy, but I expect MY MACROS in MY WORKBOOK to keep working! Ive coded like literally 12 macros in my workbook, but now every time I try and use one, I get an error saying "cannot find workbook2". "Workbook2" is gone, history, kaput! Why are all my macros "associated" with this **** workbook now, and is there any way to make workbook1's macros forget workbook2, and get on with their lives?

 

[daleyman]

my only thought on this is that you may be writing the macros in the sheet module, rather than a separate code module???

 

[keithkemble]

2 points here Lord Eck,

1 If you edit the modules (alt & F11) you can use edit/find replace to change all references to workbook 2 back to the original workbook 1.

2 If the intention is to email a sheet you need to approach it from two ways:-

either copy and paste only the areas you want excluding macro buttons, or alternatively copy and paste values.

or Alternatively you could use the workbook.add routine within a macro. this will simplify the task.

You can use the search button at the top of the page and enter the word email and you will find loads of routines.