Adding new sheet with VBA code included

[jimayers]

Hi all. I have a code that is working as expected, but I need it on every new sheet that is added via VBA
The code I need added to every new sheet is:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If CheckBox1.Value = False Then Exit Sub
If (Target.Column <> 5) Then Exit Sub
If (Target = "") Then Exit Sub
Dim cRNG As Range
Set cRNG = Worksheets(4).Range("E:E")
    With cRNG
        Call Lookup(Target)
    End With
End Sub
Sub Lookup(Target As Range)
Application.ScreenUpdating = False
Dim pFind As Range
On Error GoTo ErrorSpot
Set pFind = Sheets(1).Range("A:Z").find(What:=Target.Value, LookIn:=xlValues, LookAt:=xlPart).Offset(, -4).Resize(1, 11)
Target.Offset(, -4).Resize(1, 11).Value = pFind.Value
ErrorSpot:
MsgBox "CDCR# not found on SOMs Sheet."
Range("A3").Activate
Application.ScreenUpdating = True
End Sub

The code I am using to add new pages is:

Dim K
Dim wb As Workbook
Dim ws, wsNEW As Worksheet
Dim sNm As String
'checking if sheet already exists in workbook
Set wb = ActiveWorkbook
sNm = Cells(3, 1) 'Text in cell A3 will be tested or become new sheet
For Each ws In wb.Worksheets
    If ws.Name = sNm Then
        MsgBox ("Sheet " & sNm & " already exists")
        Exit Sub
    End If
Next
'MsgBox ("Working so far!")
'new sheet required
Set wsNEW = Sheets.Add
 wsNEW.Move After:=Worksheets(Worksheets.Count) 'move to end
 wsNEW.Name = sNm

Thanks for any help - Jim A

 

[Norie]

Do you want the code to run on every worksheet in the workbook?

 

[jimayers]

Do you want the code to run on every worksheet in the workbook?

No...I want it to be just on the new sheets added. Thanks - JA

 

[Norie]

How/when are you adding the new sheets?

 

[jimayers]

How/when are you adding the new sheets?

New sheets are being added by the code below at users discretion activated by button.

Dim K
Dim wb As Workbook
Dim ws, wsNEW As Worksheet
Dim sNm As String
'checking if sheet already exists in workbook
Set wb = ActiveWorkbook
sNm = Cells(3, 1) 'Text in cell A3 will be tested or become new sheet
For Each ws In wb.Worksheets
    If ws.Name = sNm Then
        MsgBox ("Sheet " & sNm & " already exists")
        Exit Sub
    End If
Next
'MsgBox ("Working so far!")
'new sheet required
Set wsNEW = Sheets.Add
 wsNEW.Move After:=Worksheets(Worksheets.Count) 'move to end
 wsNEW.Name = sNm

The new needs this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If CheckBox1.Value = False Then Exit Sub
If (Target.Column <> 5) Then Exit Sub
If (Target = "") Then Exit Sub
Dim cRNG As Range
Set cRNG = Worksheets(4).Range("E:E")
    With cRNG
        Call Lookup(Target)
    End With
End Sub
Sub Lookup(Target As Range)
Application.ScreenUpdating = False
Dim pFind As Range
On Error GoTo ErrorSpot
Set pFind = Sheets(1).Range("A:Z").find(What:=Target.Value, LookIn:=xlValues, LookAt:=xlPart).Offset(, -4).Resize(1, 11)
Target.Offset(, -4).Resize(1, 11).Value = pFind.Value
ErrorSpot:
MsgBox "CDCR# not found on SOMs Sheet."
Range("A3").Activate
Application.ScreenUpdating = True
End Sub

I hope that clarifies it more, thanks - Jim A

 

[Norie]

Why not create a template worksheet with the required code in it?

Then when you create a new worksheet copy the template instead of just adding a blank worksheet using Sheets.Add.

 

[jimayers]

Why not create a template worksheet with the required code in it?

Then when you create a new worksheet copy the template instead of just adding a blank worksheet using Sheets.Add.

I havent done that before, i will look it up.
thanks - JA

 

[Norie]

It's pretty straightforward to create the sheet.

1 Create a new sheet and give it an appropriate name, eg TemplateSheet.

2 Add the code the sheet.

3 (Optional) Hide the template sheet.

Then in your code to create a new sheet based on the template change your code to this.

Dim K
Dim wb As Workbook
Dim ws As Worksheet, wsNEW As Worksheet
Dim sNm As String
    
    'checking if sheet already exists in workbook
    Set wb = ActiveWorkbook

    sNm = Cells(3, 1) 'Text in cell A3 will be tested or become new sheet

    For Each ws In wb.Worksheets
        If ws.Name = sNm Then
            MsgBox ("Sheet " & sNm & " already exists")
            Exit Sub
        End If
    Next ws

    'MsgBox ("Working so far!")
    'new sheet required

    Sheets("TemplateSheet").Visible = True

    Worksheets("TemplateSheet").Copy After:=Worksheets(Worksheets.Count) 'move to end

    Set wsNEW = ActiveSheet

    wsNew.Name = sNm

    Sheets("TemplateSheet").Visible = False

 

[jimayers]

Ah, that is simple. Thanks