How to create new workbook with customize template Excel Vba

To start we must create our own template and save this template some where, in this example we save this template under Desktop and file name as mytemplate.xlxs.

The Path should be C:\Users\UserName\Desktop\mytemplate.xlxs, but we can't use UserName directly because different user the name given will different. We must replace UserName as below:

 VBA.Environ("UserProfile") & "\" & "Desktop" & "\" & "mytemplate.xlsx"

Refer my posting about Folder and Sub Folder Here.

Below completed code to create Workbook with customize Template:

Option Explicit
Sub CreateWorkBookWithCustomTemplate()
    
    'Variables Declaration
    Dim MstWB As Workbook
    Dim StrPath As String
    
    'Assign Variable according to template path
    StrPath = VBA.Environ("UserProfile") & "\" & "Desktop" & "\" & "mytemplate.xlsx"
    
    If Dir(StrPath, vbDirectory) <> "" Then
    
        'Create New Workbook with Custom template
        Set MstWB = Workbooks.Add(StrPath)
    Else
        'To prompt message if no template found
        MsgBox "No Template found. Please create template at Desktop."
    End If

End Sub

Thank You