Copy macro to a new workbook - new question
 

Workbook 1 contains a push button. Attached to the push
button is a macro that performs a couple of edits and
sends an e-mail.

I have another macro that makes copies of the data in
Workbook 1 for each business unit. Each copy contains the
same push button. I copy a sheet from Workbook 1 with the
code for the push button to the new workbooks.

However, the macro attached to the push button is the
macro from Workbook 1 and not the code on the new sheet.

Here is the code I'm using to try and get the macaro
attached to the push button in the new workbook:

Workbooks(Wcount + 2).Activate
Scount = Sheets.Count

Workbooks(Wcount + 1).Activate

Sheets("Sheet1").Select
Sheets("Sheet1").Copy After:=Workbooks(Wcount + 2).Sheets
(Scount)

ActiveSheet.Name = "Sheet " & Scount + 1

Sheets("Sheet1").Select

ActiveWorkbook.Names.Add Name:="Sheet4.ABC_Locations",
RefersToR1C1:= _
"=""Rectangle 1"""

ActiveSheet.Shapes("Rectangle 1").Select
Selection.OnAction = "Sheet4.ABC_Locations"

Any ideas or suggestions to make this code work would be
great. Thanks.

Copy macro to a new workbook - new question
 

ActiveSheet.Shapes("Rectangle 1").OnAction = Activesheet.codeName &
"!ABC_Locations"

if the Macro is declared a Public

--
Regards,
Tom Ogilvy


"JT" wrote in message
...
Workbook 1 contains a push button. Attached to the push
button is a macro that performs a couple of edits and
sends an e-mail.

I have another macro that makes copies of the data in
Workbook 1 for each business unit. Each copy contains the
same push button. I copy a sheet from Workbook 1 with the
code for the push button to the new workbooks.

However, the macro attached to the push button is the
macro from Workbook 1 and not the code on the new sheet.

Here is the code I'm using to try and get the macaro
attached to the push button in the new workbook:

Workbooks(Wcount + 2).Activate
Scount = Sheets.Count

Workbooks(Wcount + 1).Activate

Sheets("Sheet1").Select
Sheets("Sheet1").Copy After:=Workbooks(Wcount + 2).Sheets
(Scount)

ActiveSheet.Name = "Sheet " & Scount + 1

Sheets("Sheet1").Select

ActiveWorkbook.Names.Add Name:="Sheet4.ABC_Locations",
RefersToR1C1:= _
"=""Rectangle 1"""

ActiveSheet.Shapes("Rectangle 1").Select
Selection.OnAction = "Sheet4.ABC_Locations"

Any ideas or suggestions to make this code work would be
great. Thanks.