Problem with adding event code to newly created sheet

I have a problem with adding an event code to a newly created sheet.

The problem seems to only occur right after I open the Excel workbook. I use

Dim codemod As Object
codemod = ActiveWorkbook.VBProject.VBComponents(Worksheets("Sheet4").CodeName).CodeModule 

to add the code to the created sheet module but when I try to run this code right after opening the Excel workbook it gives me an error: run-time error '9' Subscript out of range. The debug points to the codemod line.

The weird part is that this error does not come up again when I change the code just a tiny bit and then change it back to the original state开发者_高级运维. After I do this the code runs as it should i.e. inserts code to the newly created sheet.

Anyone got any idea what may be the problem?

There is not much else to the code except inserting the lines but that does not seem to be the problem.

Thanks in advance

(This is my first action on S.O. so please don't shout at me when I'm doing things wrong.)

Did you concider preparing a workbook + sheet with the desired event code (Test1.xls-Sheet1). Then, in the target workbook (Test2.xls), copy that prepared sheet. The code in the target workbook would look like

Sub Demo1()
  Workbooks.Open "Test1.xls"
  Sheets("Sheet1").Copy After:=Workbooks("Test2.xls"). _
     Sheets(Workbooks("Test2.xls").Worksheets.Count)
  Workbooks("Test2.xls").Activate
End Sub

This obviously is a workaround but it works instantly.

A second option could be to prepare the 'workbook-with-one-sheet' and save it as a (sheet) template in D:\Documents and Settings\User\Application Data\Microsoft\Excel\XLSTART. In that case the code can be

Sub Demo2()
  Sheets.Add Type:="Test1"
End Sub