I'm trying to write a macro that will update the class module for a select number of sheets in 131 other workbooks. The variable being passed in is the sheets codename. It seems that the module is updated but a fatal error happens when the end with line is run while stepping through the code. Any ideas?
Private Sub code_sheet(sheetname)
code = "Private Sub Worksheet_Activate()" & vbCrLf
code = code & "filename = ""'"" + ActiveWorkbook.Name + ""'!findid""" & vbCrLf
code = code & "Application.Run filename" & vbCrLf
code = code & "End Sub" & vbCrLf & vbCrLf
code = code & "Private Sub Worksheet_Change(ByVal Target As Range)" & vbCrLf
code = code & "filename = ""'"" + ActiveWorkbook.Name + ""'!findid""" & vbCrLf
code = code & "Application.Run filename" & vbCrLf
code = code & "End Sub" & vbCrLf & vbCrLf
code = code & "Private Sub Worksheet_Deactivate()" & vbCrLf
code = code & "Application.StatusBar = False" & vbCrLf
code = code & "End Sub"
With ActiveWorkbook.VBProject.VBComponents(sheetname).CodeModule
nextline = .CountOfLines + 1
.InsertLines nextline, code
End With
End Sub
Bookmarks