Afia Kona
If you want to create a new sheet from a template using macro in Excel, you have come to the right place. Here, we will walk you through 3 easy methods to do the task effortlessly.
Download Practice Workbook
You can download the Excel file and practice while you are reading this article.
3 Methods to Create New Sheet from Template Using Macro in Excel
In the following article, we will go through 3 easy and effective methods for Excel Macro to create a new sheet from a template. Here, we used Microsoft Office 365 to do the task. You can use any available Excel version.
1. Use of Button in a Template to Create New Sheet
Here, in the following picture, you can see there is only one worksheet Sheet1 in the workbook. In this method, first, we will insert a command button. And after that, we will use an Excel Macro so that when we will click on that command button, a new sheet will be created from a template.
Let’s go through the following steps to do the task.
Step-1: Inserting Command Button
In this step, we will insert a Command Button.
- First of all, we will go to the Developer tab >> select Insert.
- Then, from ActiveX Controls >> select Command Button.
After that, you can see a plus sign on the worksheet.
- Next, keep right-clicking on the plus sign to draw a box for the command button.
As a result, you can see the Command Button.
Furthermore, we want to change the name of the Command Button.
- To do so, we will click on the Command Button >> go to the Developer tab >> select Properties.
At this point, a Properties dialog box will appear.
- Then, in the Caption box, you can see the name of the Command button.
- After that, we set the name of the Command Button as Create New Sheet.
- Along with that, we will close the Properties dialog box.
Therefore, you can see the Create New Sheet command button.
Step-2: Applying Macro
In this step, we will apply macro so that when we click on the Create New Sheet command button, a new sheet will arrive.
- In the beginning, we will go to the Developer tab >> click on Design Mode.
- Moreover, double-click on the command button.
As a result, a VBA editor window appears.
- At this point, we will type the following code in the window.
Private Sub CommandButton1_Click()
ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
End Sub
Here, we used the Add method to add a worksheet after the existing worksheet.
- After that, we will close the VBA editor window and return to our worksheet.
Afterward, we will click on the Create New Sheet command button. This will create a new sheet after Sheet1.
As a result, you can see a new excel sheet Sheet2 has been created.
If you again click on the Create New Sheet button, another Sheet will be created.
Therefore, you can create a new sheet from a template using Macro.
Read More: Macro to Create New Sheet and Copy Data in Excel (4 Examples)
Similar Readings
- Excel VBA: Create a New Workbook and Name It (6 Examples)
- Excel VBA: Add Sheet After Last (3 Ideal Examples)
- Excel VBA to Add Sheet If It Does Not Exist (with Quick Steps)
2. Using a Template with a Range of Cells to Create New Sheet
In the following picture, you can see a range of years in the Year column. Here, we will use a macro so that we can create new worksheets based on the years in a cell range.
Let’s go through the following steps to do the task.
- First of all, we will go to the Developer tab >> select Visual Basic
This will open a VBA editor window.
Besides, you can press ALT+F11 to open the VBA editor window.
Then, a VBA editor window will appear.
- Next, from the Insert tab >> select Module.
At this point, type 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
- We take create_new_sheet_from_template as the Sub.
- We declare year_range as Range and yr_list as Range.
- On Error GoTo Errorhandling handles error if we select anything other than selecting 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 or not.
- Add method is used to add names to the sheets.
- Afterward, we will close the VBA editor window and return to our worksheet.
- Furthermore, we will go to the Developer tab >> select Macros from the Code group.
This will bring out a Macro dialog box.
Here, you can press ALT+F8 to bring out the Macro dialog box,
Therefore, a Macro dialog box will appear.
Here, you can see the Macro name contains the Sub procedure name of the code.
- After that, click on the Run button.
At this point, a Create new sheet from template dialog box will appear.
- Then, select cells B5:B7 in the Pick range of years box.
- In addition, click OK.
Therefore, you can see the created new sheets named 2021, 2020, and 2019 in the workbook.
As a result, you can create a new sheet from a template using Macro.
Read More: How to Add Sheet with Name from Cell Using Excel VBA
3. Using Button to Assign a Range of Cells Before Creating a New Sheet
In the following picture, you can see a range of years in the Year column. Here, first, we will insert a Button, and after that, we will apply a macro. Therefore, when we will click on the Button, we will be able to select a range of years and this will create new worksheets based on the years in a cell range.
Let’s go through the following steps to do the task.
Step-1: Inserting Button
In this step, we will insert a Button.
- First of all, we will go to the Developer tab >> select Insert.
- Then, from Form Controls >> select Button.
As a result, you can see Button1.
Step-2: Applying Macro
In this step, we will apply macro so that when we click on the Button1, we will be able to select a range of years and this will create new worksheets based on the years in a cell range.
- In the beginning, we will follow Method-2 to bring out the Module.
- Then, we will type 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
- We take create_new_sheet_from_template_1 as the Sub.
- We declare year_range as Range and yr_list as Range.
- On Error GoTo Errorhandling handles error if we select anything other than selecting 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 or not.
- Add method is used to add names to the sheets.
- Afterward, we will close the VBA editor window and return to our worksheet.
- After that, right-click on Button1 >> select Assign Macro from the Context Menu.
At this point, an Assign Macro dialog box will appear.
- Next, select Create_new_sheet-from_template_1 >> click OK.
- At this point, we will click on Button 1.
Next, a Create new sheet from template dialog box will appear.
- Then, select cells B5:B8 in the Pick range of years box.
- In addition, click OK.
Then, you can see the created new sheets named 2022, 2021, 2020, and 2019 in the workbook.
Hence, you can create a new sheet from a template using Macro.
Read More: How to Add Sheet with Name in Excel VBA (6 Easy Ways)
Conclusion
Here, we tried to show you 3 methods to create a new sheet from a template using Macro in Excel. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below. Please visit our website Exceldemy to explore more.
