How do I create/use a custom or default sheet template

Important message to visitors of this page

Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.

Luckily, Ron was kind enough to allow me to publish all of his Excel content here.

Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.

Kind regards

Jan Karel Pieterse

Note : If you use Excel on a Mac read the information on this page.

If you want to insert a new specific worksheet to your workbook with a nice layout and maybe a few formulas on a regular basis then you have two options.

A) Make a copy each time of a clean worksheet with your layout

1: Select the sheet that you want to copy
2: Right click on the sheet tab and choose "Move or Copy"
3: Check the "Create Copy" checkbox
4: Be sure that the “To book” dropdown show the name of your file
5: Press OK

Or hold the CTRL key down and drag the sheet tab to the right or left with your mouse.
When you release the mouse button you will see the copy of the sheet.

B) Use a Sheet template(xlt, xltx or xltm)

How do I create one:
1: Open a new workbook and delete all worksheets except the first one.
2: Change all the things you want in this worksheet
3: Then use File>Save As to save the file with the name you want as a Template (xlt, xltx or xltm)
In this folder (default folder if you save as a template)

Note: Ron is the user name in the path

C:\Documents and Settings\Ron\Application Data\Microsoft\Templates

In Vista-windows 7 you can find the folder here
C:\Users\Ron\AppData\Roaming\Microsoft\Templates

With code you can find the correct path with this line
MsgBox Application.TemplatesPath

How do I insert a sheet template in my workbook:

1: Right click on a sheet tab and choose "Insert"
2: Select your template
3: OK

Or you can use a macro to insert the sheet template. The example below will insert the template MySheetTemplate.xlt after the last sheet in your workbook with the name: Today's date.

You can use this macro for example if you want to insert a new worksheet based on this template
every day with the date as worksheet name.

Sub Insert_Sheet_Template()
    Dim sh As Worksheet
    Dim shName As String

    'name of the sheet template
    shName = "MySheetTemplate.xlt"

    'Insert sheet template
    With ThisWorkbook
        Set sh = Sheets.Add(Type:=Application.TemplatesPath & shName, _
                            after:=.Sheets(.Sheets.Count))
    End With

    'Give the sheet a name, today's date in this example
    On Error Resume Next
    sh.Name = Format(Date, "yyyy-mmm-dd")
    If Err.Number > 0 Then
        MsgBox "Change the name of Sheet : " & sh.Name & " manually"
        Err.Clear
    End If
    On Error GoTo 0
End Sub

If you remove the name part in this macro and the sheet name in the template is "ron" the first time
the name of the sheet is "ron" the second time the name is "ron(2)" and the third time "ron(3)" and .....

With the macro example below you can select the template that you want to insert

Sub Select_The_Sheet_Template()
    Dim MyPath As String
    Dim SaveDriveDir As String
    Dim FileToOpen As Variant

    SaveDriveDir = CurDir
    MyPath = Application.TemplatesPath
    ChDrive MyPath
    ChDir MyPath

    FileToOpen = Application.GetOpenFilename("Excel Templates (*.xlt*),*.xlt*")
    If FileToOpen <> False Then
        Sheets.Add Type:=FileToOpen
    End If

    ChDrive SaveDriveDir
    ChDir SaveDriveDir
End Sub

Change the Default Excel sheet template

In Excel 97-2013 you can insert a new default worksheet like this:
Right click on a sheet tab and click on "Insert" and choose "Worksheet"
Or you can use the shortcut Shift+F11

Or you can use the Excel menu or the Ribbon. In Excel 97-2003 you can use "Insert>Worksheet"
In Excel 2007-2013 on the Home tab in the Cells group choose "Insert>Insert sheet" or click on the "Insert worksheet" button next to the last sheet tab

If you want to change the settings of this worksheet you can create your own sheet template. Open a new workbook and delete all worksheets except the first one. Change the things you want in this worksheet (Font/background color, font and font size and ?) Then use File>Save As to save the file as a Template (xlt, xltx or xltm) with the name Sheet in the XLSTART folder. Every worksheet you insert now is based on this template worksheet.

Where is the Excel startup folder ?

Note: This is a hidden folder, change the view in Windows explorer so it show hidden files and folders. Note: Ron is the user name in the path

C:\Documents and Settings\Ron\Application Data\Microsoft\Excel\XLSTART
Or in Vista-Windows 7
C:\Users\Ron\AppData\Roaming\Microsoft\Excel\XLSTART

With code you can find the correct path with this code line
MsgBox Application.StartupPath

Ron de BruinExcel Automation