Add custom sheet when a new sheet is created

PankajMaheshwari

New Member
Joined
Sep 7, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
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?

VBA Code:
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.
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,343
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.
VBA 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
 
Solution

PankajMaheshwari

New Member
Joined
Sep 7, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
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

New Member
Joined
Sep 7, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
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

BatCoder
Joined
Feb 28, 2002
Messages
1,343
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,123
Messages
5,622,870
Members
415,935
Latest member
kes1973

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top