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
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
With code you can find the correct path with this line
How do I insert a sheet template in my workbook:
1: Right click on a sheet tab and choose "Insert"
2: Select your template
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
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
Or in Vista-Windows 7
With code you can find the correct path with this code line