My Macros Are Vanishing!

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,557
Office Version
  1. 365
Platform
  1. Windows
I created some simple macros, such as:

Sub FindFirstEmptyRow()
Cells(Rows.Count, 1).End(xlUp).Offset(1).Select
End Sub

There are nine tabs all with the same macro. When I save each tab as its own workbook, as XLSM file, save to my desktop, then send to a coworker, the macros have vanished! Why do they do this?

I also have the same problem with hyperlinks...
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
If the macros are in a standard module, they belong to the workbook, not to any individual worksheet. If you move or copy a single worksheet to a new workbook, the macros do not transfer.
 
Upvote 0
I can't reproduce that behaviour if the macro is in the module for the worksheet that you are copying to a new workbook and saving (Excel 2007 12.0.6545.5000 SP2 MSO (12.0.6545.5004)).
 
Upvote 0
If the macros are in a standard module, they belong to the workbook, not to any individual worksheet. If you move or copy a single worksheet to a new workbook, the macros do not transfer.

What is the best way to fix that?
 
Upvote 0
You just have to get the macros in the workbooks you are creating. Simplest might be to copy the entire workbook, then delete unwanted tabs.

Other possibilities:
  • Copy tabs into a workbook that has this code module in it (a "template").
  • Find one of the routines here on the board that copies code modules.
  • Don't send out workbooks that require code (meant seriously - I try not to send macro'd up books to users, who may not have macros enabled anyway).
  • Put the function on every sheet as a sheet class function.
 
Upvote 0
You just have to get the macros in the workbooks you are creating. Simplest might be to copy the entire workbook, then delete unwanted tabs.


Other possibilities:
  • Copy tabs into a workbook that has this code module in it (a "template").
  • Find one of the routines here on the board that copies code modules.
  • Don't send out workbooks that require code (meant seriously - I try not to send macro'd up books to users, who may not have macros enabled anyway).
  • Put the function on every sheet as a sheet class function.

Thank you.

Do you know why the same thing happens with hyperlinks? I have a document hyperlinked to our shared drive but when people open and save to their desktop, the link becomes invalid.
 
Upvote 0
Any number of things can go wrong with hyperlinks. The hyperlink may contain a relative path. The user may have a different drive mapping. (I avoid them as unreliable, personally) - I'd suggest you go over to someone's computer where the link isn't working and investigate yourself - examine it's properties and see what's gone wrong. Sorry that I don't have a better answer - I may not even be sure what exactly you mean when you say you send someone a hyperlink. Do you mean sending a workbook with hyperlinks in it? I would definitely avoid that - they get wonky in my own workbooks, much less trying to send them around to others.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,222,096
Messages
6,163,907
Members
451,865
Latest member
dunworthc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top