Add custom sheet when a new sheet is created

[PankajMaheshwari]

Hi Experts,

I have a file in which the first sheet is my Template sheet and remaining other sheets have similar template but has data in it. What I'm trying to achieve here is whenever someone creates a new sheet, it doesn't just create a blank sheet, but it creates the standard template which is my first sheet in the file.

I was trying to trigger a new/insert sheet event and then it goes to the first tab, copy it and then comes back to the new sheet and paste entire template. But I failed.

So my questions are 1. Can someone help me in modifying my below code. 2. Is there a better way of doing this?

Private Sub Workbook_NewSheet(ByVal Sh As Object)

    Sheets("Template").Select
    Cells.Select
    Selection.Copy
    
    Sheets("Sh").Select
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
    ActiveWindow.DisplayGridlines = False
    ActiveWindow.Zoom = 90

End Sub

Thanks in advance for any support. You guys have been wonderful so far.

 

[smozgur]

If you really need to do that by listening to the new sheet creation event, which means you want them to be able to create a copy of your template worksheet whenever they attempt to create a new sheet, then you can delete the newly created sheet in the event code, and copy the Template worksheet as the last worksheet instead.

Try the following code.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    ' Make sure if the created object is a worksheet
    ' Otherwise stop execution
    If TypeName(Sh) <> "Worksheet" Then
        Exit Sub
    End If
    
    ' Disable alerts, so we don't see the delete confirmation
    Application.DisplayAlerts = False
    ' Delete the new worksheet
    Sh.Delete
    ' Enable alerts back - this is important
    Application.DisplayAlerts = True
    
    With ThisWorkbook
        ' Copy the Template worksheet as the last worksheet in the workbook
        .Worksheets("Template").Copy After:=.Worksheets(.Worksheets.Count)
    End With
End Sub

 

[PankajMaheshwari]

Hi @smozgur It's not working for some reason. When I add a worksheet, it's just a normal blank worksheet. Should I enable anything for this to work? Thanks a lot for your help.

 

[PankajMaheshwari]

Hi @smozgur I copy pasted my code in a module and that was the issue. When I pasted my code in "Thisworkbook", it worked. Thanks a lot.

 

[smozgur]

Hi @smozgur I copy pasted my code in a module and that was the issue. When I pasted my code in "Thisworkbook", it worked. Thanks a lot.

Right, it is a class event procedure that is supposed to be in the object module instead of a standard module. I assumed that you already knew that since you posted the event code in the original question.

Glad to hear you got it working.