Excel VBA Workbook code out of range when opened

[Xyphus]

Hey

I am new to VBA and have mainly built this thanks to ChatGPT, forums and gluing code together, I am having a problem with this code in that when I first open my document and this is activated I get a 'Run-time error '9': Subscript out of range' BUT if I go into the VBA editor in Excel and click reset this code works exactly how it is meant to, this is the code

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim code As String
code = "Private Sub Worksheet_Change(ByVal Target As Range)" & vbCrLf & _
" If Not Intersect(Target, Me.Range(""D2"")) Is Nothing Then" & vbCrLf & _
" HideRowsBasedOnCriteria" & vbCrLf & _
" End If" & vbCrLf & _
"End Sub"

With ThisWorkbook.VBProject.VBComponents(Sh.CodeName).codeModule
.InsertLines .CountOfLines + 1, code
End With
End Sub

the purpose of this is to paste this code

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("D2")) Is Nothing Then
HideRowsBasedOnCriteria
End If
End Sub

into a new sheet on creation that works as a filter using values from a hidden column

 

[igold]

Hi, welcome to the MrExcel forum.

Perhaps you could give us a little more detail on what you are trying to accomplish. I am not sure that ChatGPT was your friend here... It appears that you are trying write code with code, which is not such a great idea and I believe that you have to grant Excel certain permissions to do that and it becomes a security risk.

 

[Xyphus]

Hi igold

ChatGPT did mention the security risk and after reading your comment I am considering other methods for this,

But as for more details, the purpose of the workbook is for QA test sheets with some functionality to save some time in the long run, one macro that is working is attached to a button that copies the cells data, formatting and column width into a new worksheet and names that worksheet today's date with a character added if sheet already exists, the part of the code I pasted in my original comment is for a filter that would allow us to use one workbook for two similar but different forms of testing, this is currently done by looking at the value of cell D2 and hides a row if a certain value is in the hidden column E X=hide Y / if Y hide X that I wanted to run automatically if D2 was altered.

 

[Engberg]

I'm not sure what the source of your error is, but instead of using code to write code when a new sheet is created, you could copy a template sheet that already contains the code you need. Copying a sheet also copies the sheet-specific VBA module.

Sub copyActiveSheet()
ActiveSheet.Copy After:=ActiveSheet
End Sub

 

[Engberg]

Another example if you want to use a hidden template sheet that is only used for getting the VBA into the new sheet.

Sub copySheet()
Sheets("Template Sheet").Visible = True
Sheets("Template Sheet").Copy After:=Sheets(Worksheets.Count)
Sheets("Template Sheet").Visible = False
ActiveSheet.Name = "new Sheet" & Worksheets.Count
End Sub

 

[Xyphus]

Thanks, Engberg
this solved my issue, because I was using chat GPT for this it gave exactly what I asked for rather than what would have been simpler being just plan old copy