How to Create a New Sheet from a Template Using a Macro in Excel – 3 Methods

Method 1 – Using a Button in a Template to Create a New Sheet

There is only one worksheet Sheet1 in the workbook.

Insert a command button:

Step 1: Inserting Command Button

• Go to the Developer tab >> select Insert.
• In ActiveX Controls >> select Command.

You will see a plus sign.

• Right-click the plus sign to draw a box for the command button.

The Command Button is created.

Change the name of the Command Button.

• Click the Command Button >> go to the Developer tab >> select Properties.

The Properties dialog box will be displayed.

• In Caption, you can see the name of the Command button.

• Rename it as Create New Sheet.
• Close the Properties dialog box.

This is the output.

Step 2: Applying a Macro

• Go to the Developer tab >> click Design Mode.
• Double-click the command button.

The VBA editor window is displayed.

• Enter the following code in the window.

Private Sub CommandButton1_Click()
ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
End Sub

The Add method was used to add a worksheet to the existing worksheet.

• Close the VBA editor window and go back to the worksheet.

Click the Create New Sheet command button. It will create a new sheet after Sheet1.

Sheet2 is created.
If you click the Create New Sheet button, another Sheet will be created.

Read More: Excel Macro to Create New Sheet and Copy Data

Method 2 – Using a Template with a Range of Cells to Create a New Sheet

There is a range of years in the Year column.

• Go to the Developer tab >> select Visual Basic.

The VBA editor window will be displayed.
You can also press ALT+F11 to open the VBA editor window.

• In Insert  >> select Module.

• Enter the following code in the Module.

Sub create_new_sheet_from_template()

Dim year_range As Range
Dim yr_list As Range

On Error GoTo Errorhandling

Set year_range = Application.InputBox(Prompt:="Pick range of years:", _
Title:="create new sheet from template", _
Default:=Selection.Address, Type:=8)

For Each yr_list In year_range
  If yr_list <> "" Then
    Sheets.Add.Name = yr_list
  End If
Next yr_list

Errorhandling:

End Sub

• Close the VBA editor window and go back to the worksheet.
• Go to the Developer tab >> Select Macros in Code.

The Macro dialog box will be displayed.
You can press ALT+F8 to open the Macro dialog box,

The Macro name contains the Sub procedure name of the code.

• Click Run.

The Create new sheet from template dialog box will be displayed.

• Select B5:B7 in Pick range of years.
• Click OK.

New sheets named 2021, 2020, and 2019  are created in the workbook.

Read More: How to Add Sheet with Name from Cell Using Excel VBA

Method 3 – Using a Button to Assign a Range of Cells Before Creating a New Sheet

There is a range of years in the Year column.

Step 1: Inserting Button

• Go to the Developer tab >> select Insert.
• In Form Controls >> select Button.

You can see Button1.

Step 2: Applying a Macro

• Follow Method 2 to open a Module.
• Enter the following code in the Module.

Sub create_new_sheet_from_template_1()
Dim year_range As range
Dim yr_list As range
On Error GoTo Errorhandling
Set yearrange = Application.InputBox(Prompt:="Pick range of years:", _
Title:="create new sheet from template", _
Default:=Selection.Address, Type:=8)
For Each yr_list In year_range
  If yr_list <> "" Then
  Sheets.Add.Name = yr_list
  End If
Next yr_list
Errorhandling:
End Sub

• Close the VBA editor window and go back to your worksheet.
• Right-click Button1 >> select Assign Macro in the Context Menu.

The Assign Macro dialog box will be displayed.

• Select Create_new_sheet-from_template_1 >> click OK.

• Click Button 1.

The Create new sheet from template dialog box will be displayed.

• Select B5:B8 in Pick range of years.
• Click OK.

New sheets named 2022, 2021, 2020, and 2019 were created in the workbook.

Download Practice Workbook

Download the Excel file and practice.

Related Articles

• Excel VBA to Add Sheet with Variable Name
• Excel VBA: Add Sheet After Last
• Excel VBA to Add Sheet If It Does Not Exist
• How to Add Sheet with Name in Excel VBA
• How to Add Sheet After Current One with Excel VBA
• Excel Macro: Create New Sheet and Rename