Add VBA code to multiple WorkSheets
 

The following code works for one worksheet but when I try to use it for
multiple worksheets, I get the error message: Excel has created errors,
Windows will shut the program down. A log file has been created. Must I do
something special when adding code to multiple worksheets? Thanks in advance.

Sub addvba()
Dim s As String
Dim DEPT2 As String
Dim dept As Integer

Application.ScreenUpdating = False

For dept = 9 To 10

Select Case dept
Case 9
DEPT2 = "ALL"
Case 10
DEPT2 = "OTHER"
End Select


Call addactivate(DEPT2)

Next dept
End Sub


Sub addactivate(DEPT2 As String)
Dim StartLine As Long
Dim s As String
Dim LINENUM As Integer

s = ActiveWorkbook.Worksheets(DEPT2).CodeName

With ActiveWorkbook.VBProject.VBComponents(s).CodeModul e
StartLine = .CreateEventProc("Activate", "Worksheet")

LINENUM = .CountOfLines
Debug.Print LINENUM
.InsertLines LINENUM - 1, _
"If lastAddress < """" Then Range(lastAddress).Select" & Chr(13)

LINENUM = 6
.InsertLines LINENUM, _
"Private Sub Worksheet_SelectionChange(ByVal Target As Range)" & Chr(13)
& _
"lastAddress = Target.Address" & Chr(13) & _
"End Sub"

End With

End Sub