Ashley91
11-04-2021, 06:48 PM
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/showthread.php?69344-Concatenate-columns-without-formula-in-private-sub-worksheet_change
I found this on a different site:
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
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. :banghead:
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 :banghead:
I am using Excel 365. Thanks to anyone who will help enlighten me :help
I found this on a different site:
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
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. :banghead:
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 :banghead:
I am using Excel 365. Thanks to anyone who will help enlighten me :help