Sub AddShtAndCodes() Dim NewSht As Worksheet, MyCodes As String Set NewSht = Worksheets.Add NewSht.Name = "test" MyCodes = "Private Sub Worksheet_Activate()" & vbCrLf & "Msgbox ""test""" & vbCrLf & "End sub" Application.VBE.ActiveVBProject.VBComponents(NewSht.CodeName).CodeModule.InsertLines 1, MyCodes End Sub