Method 1 – Using a Button in a Template to Create a New Sheet
There is only one worksheet Sheet1 in the workbook.
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
Code Breakdown
- create_new_sheet_from_template is the Sub.
- year_range is declared as Range and yr_list as Range.
- On Error GoTo Errorhandling handles errors if you select anything other than the range of years.
- The For loop continues to create new sheets until it finds the last selected year.
- The If statement is used to check whether the variable name is empty.
- Add method is used to add names to the sheets.
- 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
Code Breakdown
- _new_sheet_from_template_1 is the Sub.
- year_range is declared as Range and yr_list as Range.
- On Error GoTo Errorhandling handles error if you select anything other than the range of years.
- The For loop continues to create new sheets until it finds the last selected year.
- The IF statement is used to check whether the variable name is empty.
- Add method is used to add names to the sheets.
- 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
