My Macros Are Vanishing!

[Justinian]

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...

 

[JoeMo]

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]

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]

Any idea why the same thing occurs with my hyperlinks?

 

[Justinian]

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?

 

[Justinian]

How do I fix? Private sub??

 

[xenou]

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]

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]

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.