Copying worksheet with macro

Hello,

I have in Sheet1 one macro named Macro1. When I try to copy Sheet1 from c:\Workbook1.xls to c:\Workbook2.xls it copies the code with Sheet1 but in Assign Macro there is still reference to c:\Workbook1.xls, so it looks like C:\Workbook1.xls'!Macro1 instead C:\Workbook2.xls'!Macro1. How can I fix this?

To copy worksheet I use .Copy from Workbooks().Sheets().Copy....

Lets say I have to workbooks opened Workbook1 and Workbook2.

Workbook1 consists of worksheet "TBC" with Macro named Macro1 saved in Microsoft Excel Object -> Sheet1(TBC).

I copy Sheet (TBC) from Workbook1 to Workbook2 using Workbooks("Workbook1").Sheets("TBC").Copy Before:=Workbooks("Workbook2").Sheets("Sheet1") and thats works fine. Sheet TBC is copied to Workbook2 with macro in Excel Object -> Sheet1(TBC).

The problem is that the name or path to macro is not changed. When I look in assign macro i have Workbook1.xls!Sheet1.Macro1 instead of Workbook2.xls!Sheet1.Macro1 (forgot to mentioned that macro is assigned to checkbox)

I know I can fix this using

ActiveSheet.Shapes("Checkbox").Select
Selection.OnAction = "Sheet1.Macro1"

But it should be in ThisWorkbook module in Open action. Is there possibility to change that name/path form Excel Object -> Sheet1 module which is copied with Sheet("TBC")? That way I wouldn't have to importe code as its already copied with Sheet

Self bump. Any ideas?

Ok, lets do it another way. How can I put code into active workbook? I found a macro:

Sub CopyModule()
Set wbcodemod = ActiveWorkbook.VBProject.VBComponents(ActiveWorkbook.CodeName).CodeModule
Set codemod = ActiveWorkbook.VBProject.VBComponents(ActiveWorkbook.CodeName).CodeModule
LineNum = codemod.countoflines + 1
With codemod
.insertLines LineNum, "Private Sub Workbook_Open()"
LineNum = LineNum + 1
.insertLines LineNum, "ActiveSheet.CheckBoxes('chkAll').Select"
LineNum = LineNum + 1
.insertLines LineNum, "Selection.OnAction = 'Sheet1.chkAll_Click'"
LineNum = LineNum + 1
.insertLines LineNum, "End Sub"
End With
End Sub

But it aint working I got Subscript out of range when I debug line

Set wbcodemod = ActiveWorkbook.VBProject.VBComponents(ActiveWorkbook.CodeName).CodeModule

is highlited