How to Create New Sheet from Template Using Macro in Excel

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.

excel macro create new sheet from 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.

excel macro create new sheet from template

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.

excel macro create new sheet from template

  • 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.

excel macro create new sheet from template


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.

excel macro create new sheet from template

  • 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.

excel macro create new sheet from template

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


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.

excel macro create new sheet from template

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.

excel macro create new sheet from template

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,

excel macro create new sheet from template

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.

excel macro create new sheet from template

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.

excel macro create new sheet from template

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.

excel macro create new sheet from template


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.

excel macro create new sheet from template

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.

excel macro create new sheet from template

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.

excel macro create new sheet from template

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.


Related Articles

Afia

Afia

Hello, I am Afia Aziz Kona. I graduated in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology (BUET). I have an immense interest in technical writing and content development, therefore, I am working as a content developer at Exceldemy. In my spare time, I travel, watch movies, and cook different dishes.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo