My Macros Are Vanishing!

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,473
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...
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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)).
 

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,473
Office Version
  1. 365
Platform
  1. Windows
Any idea why the same thing occurs with my hyperlinks?
 

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,473
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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?
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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.
 

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,473
Office Version
  1. 365
Platform
  1. Windows
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.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,653
Members
414,083
Latest member
Mrsash

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
Top