How to copy code module from one worksheet to another.

Basically I would think this should be pretty simple, but everything that I have tried is not working for me. First, I am using Excel 2016 and working with a workbook where when needed it copies one worksheet called template to another. The template worksheet has a simple Print button on it. The copy procedure copies the print button OK, but the simple CommandButton1_Click code which simple contains "PrintSelection" does not get copied over. I want to automate this so that other users can add new sheets and the Print button will be ready for their use.

I have researched and tried a number of things. I have added the reference to MS VB for Applications Extensibility 5.3 to the VB project window and I have tried a couple of different subs but they fail with the message "subscript out or range" errors.

One example is"
"Sub copySheetCode()

Dim src As CodeModule
Dim dest As CodeModule

Set src = ThisWorkbook.VBProject.VBComponents("Template").CodeModule

Set dest = ThisWorkbook("test").VBProject.VBComponents _
.CodeModule

dest.DeleteLines 1, dest.CountOfLines
dest.AddFromString src.Lines(1, src.CountOfLines)

End Sub
"

The error occurs on the first like starting with "Set src"

Does anyone have any idea what I might need to do to resolve this, or any other way of achieving what I want to do?

Thanks in advance, all help is greatly appreciated.

The code is in the worksheet code area. The "Thisworkbook is what I copied from my search. I have also tried:

[code]<Sub copyCode()
Dim CodeCopy As VBIDE.CodeModule
Dim CodePaste As VBIDE.CodeModule
Dim numLines As Integer

Set CodeCopy = ActiveWorkbook.VBProject.VBComponents("Template").CodeModule
Set CodePaste = ActiveWorkbook.VBProject.VBComponents("test").CodeModule

numLines = CodeCopy.CountOfLines
'Use this line to erase all code that might already be in sheet3:
'If CodePaste.CountOfLines > 1 Then CodePaste.DeleteLines 1, CodePaste.CountOfLines

CodePaste.AddFromString CodeCopy.Lines(1, numLines)

End Sub>[code]

Both give me the same error: run time error 9, "subscript out or range"

It still results in the same error 9 subscript out of range. Also I am not trying to copy code into a module but into the same area where if you placed code when the worksheet was activated or deactivated would appear. When you add the commandbutton1 control and then right click on it to view code, it places the code in this same section for the worksheet control codes.

Please Login or Register  to view this content.

I am simply trying to find a way to automate placing this code there since the copy of the template sheet is created in a new worksheet with all the formulas, etc and the print commandbutton1 control, but the above code is not copied over into that worksheet.

Never mind the caveat. I had to use the Me. construct to get around what was happening to my template. Thanks to Leith Ross for that long sought after solution.