Moving Macros with Sheets to New Workbook

[Guest]

Thanks for taking the time to read my question.

I have a workbook that has a lot of sheets in it. I'd like to be able to
make a copy of a sheet and place it in a new workbook, then e-mail the new
workbook. I want to send just the one sheet becuase each sheet is for a
different customer, and the information is sensitive.

My problem is... I have macros on each sheet. When I copy a sheet into a
new workbook, the macro comes with it, but the button on the sheet still
points to the macro in the original workbook. You have to right mouse click
"Assign Macro" and choose the macro in the current workbook. I don't want
the clients to have to do this.

Is there a property setting that tells Excel to use the macro in the new
workbook instead of the old workbook.

Thanks again for the help.

Brad

 

[Guest]

The answer is:

Create a button using the Control Toolbox instead of the Forms toolbar.

Doing this will embed the code to the button, not the workbook, and the
macro will move with the sheet.

Brad

 

[Tom Ogilvy]

A workaround might be
activesheet.copy
for each btn in activesheet.buttons
btn.OnAction = application.Substitute(btn.OnAction, _
oldfilename,newfilename)
Next

Is the code for the buttons in the sheet module?