Problem:
I have created a one-sheet template with .xltm extension that includes a footer, header, and altered font. I want that when creating/adding a new sheet, the settings should be the same as in the created template.I saved this template in the path C:\Users\User\AppData\Roaming\Microsoft\Templates
Private Sub Workbook_NewSheet(ByVal Sh As Object)
On Error GoTo enable_events
Dim sheet_name As String
sheet_name = Sh.Name
Application.EnableEvents = False
' code to add new workwheet without re-triggering this event handler
Application.ScreenUpdating = False
ThisWorkbook.Worksheets("Template").Visible = True
ThisWorkbook.Worksheets("Template").Copy after:=Sh
ThisWorkbook.Worksheets("Template").Visible = xlVeryHidden
Application.DisplayAlerts = False
Sh.Delete
ActiveSheet.Name = sheet_name
Application.ScreenUpdating = True
enable_events:
Application.EnableEvents = True
End Sub
This example calls your existing procedure to allow you to choose the template, which has the worksheet. You mentioned that your existing code does not bring in your template, so you may want to try to modify it using code from my prior answer. Like the other code example in this answer, this code needs to be in the "ThisWorkbook" module.
Private Sub Workbook_NewSheet(ByVal Sh As Object)
On Error GoTo enable_events
Dim sheet_name As String
sheet_name = Sh.Name
Application.EnableEvents = False
' code to add new workwheet without re-triggering this event handler
Application.ScreenUpdating = False
Select_The_Sheet_Template ' call your existing code
Application.DisplayAlerts = False
Sh.Delete
ActiveSheet.Name = sheet_name
Application.ScreenUpdating = True
enable_events:
Application.EnableEvents = True
End Sub
Here's one more example that reads the template from a workbook in a fixed location so any workbook with this code on its "ThisWorkbook" module could get the template. This example also traps for an error, re-enabling the event handler in case of an error after the event handling is turned off for the workbook
Private Sub Workbook_NewSheet(ByVal Sh As Object)
On Error GoTo enable_events
Dim template_path As String
template_path = "C:\Users\User\AppData\Roaming\Microsoft\Templates\TemplateSheet.xltm"
Dim wb As Workbook
Application.EnableEvents = False
Application.ScreenUpdating = False
Set wb = Workbooks.Open(template_path)
wb.Worksheets(1).Copy after:=Sh
wb.Close False
Application.DisplayAlerts = False
Sh.Delete
Application.ScreenUpdating = True
enable_events:
Application.EnableEvents = True
End Sub