Query re: macros and copying worksheets

[scarlett3]

I have two Excel files and both have the same macro entitled 'sort'. There is a button on each worksheet that the user can press and it will sort the data in that worksheet using that macro.

Here is the problem...

I need to copy some of the worksheets from Excel file A (my own personal use) to Excel file B (for other people to use). However, the button on the copied over worksheets is assigned to the 'sort' macro in Excel file A. I need to manually edit the button in each worksheet to assign it to the 'sort' macro in Excel file B.

Is there a way for the worksheets to be copied over and the button on each worksheet to be assigned to the macro in the destination file, not the original file?

Thanks for your help!

 

[tonyyy]

scarlett3,

Below is one approach to copy the ActiveSheet to another workbook...

Sub CopySheetAndButton1()
Dim wb1 As Workbook
Set wb1 = Workbooks("ExcelFileB.xlsm")
ActiveSheet.Copy before:=wb1.Sheets(1)
wb1.Sheets(1).Buttons(1).OnAction = wb1.Name & "!MacroName"
End Sub

The code will also assign the macro in the destination file to the button. (I'm assuming there's only one button on the sheet.)

Cheers,

Tony