Runing a macro when a new worksheet is created

When i create a new worksheet by copying an existing worksheet of the workbook i need to run a macro which sets values for a worsheet in a different workbook.I used the following vb code,

   Dim m_lngNSheets As Long
   
   Private Sub Workbook_Open()
   m_lngNSheets = ThisWorkbook.Sheets.Count
   End Sub

1   Private Sub Workbook_SheetActivate(ByVal Sh As Object)
2   Dim ShtName$
3   Dim lRow As Long
4   If ThisWorkbook.Sheets.Count > m_lngNSheets Then
5   ShtName = InputBox("Enter Sheet Name")
6   Sheets(Sheets.Count).Name = ShtName
7   Sheets(ShtName).Range("c2").Value = InputBox("Enter Employee Name")
8   Sheets(ShtName).Range("l3").Value = InputBox("Enter Designation")
9   Sheets(ShtName).Range("i2").Value = InputBox("Enter DOJ")
10   Sheets(ShtName).Range("B9:J39").ClearContents
11  Sheets(ShtName).Range("O9:P39").ClearContents
12   Application.Workbooks.Open ("H:\final\2012-05 Attendance Report -1 (On Role).xlsx"), False
13   For lRow = 3 To 100
14   If Workbooks("2012-05 Attendance Report -1 (On Role).xlsx").Worksheets("Sheet1").Range("C" & lRow).Value = vbNullString Then
15   Workbooks("2012-05 Attendance Report -1 (On Role).xlsx").Worksheets("Sheet1").Range("C" & lRow).Value = Workbooks("2012_05_Time sheet -1 (On Role).xlsm").Worksheets(ShtName).Range(C2).Value
16   Workbooks("2012-05 Attendance Report -1 (On Role).xlsx").Worksheets("Sheet1").Range("D" & lRow).Value = Workbooks("2012_05_Time sheet -1 (On Role).xlsm").Worksheets(ShtName).Range(L3).Value
17   Workbooks("2012-05 Attendance Report -1 (On Role).xlsx").Worksheets("Sheet1").Range("E" & lRow).Value = "E-Publishing"
18   Workbooks("2012-05 Attendance Report -1 (On Role).xlsx").Worksheets("Sheet1").Range("F" & lRow).Value = Workbooks("2012_05_Time sheet -1 (On Role).xlsm").Worksheets(ShtName).Range(I2).Value
19   GoTo Save
20   Else
21   End If
22   Next lRow
23   Save:
24   Workbooks("Copy of Productivity Tracking Sheet.xlsm").Close Savechanges:=True
25   End If
26   m_lngNSheets = ThisWorkbook.Sheets.Count
27   End Sub

I have two problems with this code.
(1)At line 4 the value of m_lngNSheets is "0" which must be "38" based on my workbook count.
(2)I get a run-time error '1004' (Application-defined or object-defined error) at line 15.

Can anyone help me with this code..