My workbook contains a large sheet (240 columns) which is generated each year anew from a template sheet. The user has to be able to see a userform and this is made possible by clicking anywhere in row(2). There is code in the sheet module fired by Selection_Change event to do this
I am trying to transfer this code from the Template sheet to the worksheet as it is generated, and have found some code that I have modified to do this.
Sub TemplateCode(SheetName)
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(SheetName).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
This fails with a "Substrate out of range " error at line starts Set Codecopy=.
Sheet "Template" does exist and has code in Worksheet_SelectionChange event. I expect that the next line might well come up with an error message too.
I have ticked the MSVB extension in references.
I have gone wrong somewhere and would welcome some help.
John