Hello, so what I am trying to achieve is, on button click the file will create a new workbook and add n number of sheets and n number of tables in each sheet, which is already done thanks to p45cal and arnelgp's help. And then on each of those sheets, the code provided by p45cal or arnelgp (again) here http://www.vbaexpress.com/forum/show...rksheet_change
I found this on a different site:
Which works however if I change the sheet name to my sheet name (example 15 minutes Inter - 1st Week Nov), it's giving an error saying Subscript out of range. But that's impossible because my sheet is named exactly like that and I am running the code above on that same workbook, unless I am misunderstanding what that error means.Sub AddCode() Dim ws As Worksheet: Set ws = Worksheets("Sheet1") Dim wb As Workbook: Set wb = ActiveWorkbook Dim code As String: code = "Sub Duh()" & vbCrLf & " debug.print(""I'm Here!"")" & vbCrLf & "End Sub" Dim lineCount As Integer With wb.VBProject.VBComponents(ws.Name).CodeModule lineCount = .CountOfLines If lineCount > 0 Then .DeleteLines 1, lineCount End If .AddFromString code End With End Sub
Also there will be 4 or 5 sheets and the code above will only add the worksheet change event to just the indicated sheet and I need the code be added to all the sheets in that workbook
I am using Excel 365. Thanks to anyone who will help enlighten me