Home > other >  Insert template into new sheet Excel - VBA
Insert template into new sheet Excel - VBA

Time:03-21

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

I found the following enter image description here

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
  • Related