How to add Excel sheet by VBA code
- By using VBA, you may add one or more Excel sheets easily.
- A sheet can be added at the beginning, end, or in between i.e. at the specified position
- You may name the newly added sheet as well
- The added sheet becomes active
Which method is used to create new sheet in VBA
A new sheet can be added by Sheets object’s Add method. For example,
Sheets.Add
This code will add a sheet before the active sheet in your Workbook.
Let us look at a few examples with macros and images for adding sheets.
Add a sheet before active sheet example
In the first example, we will add a sheet before the existing active sheet. For an idea, this is our sample sheet before we execute the code:
Follow these steps for adding a new sheet before the active sheet:
Step 1:
Go to the “DEVELOPER” tab and locate/click “Visual Basic” as shown below:
It should open the VBA code editor.
Or press “Alt + F11” shortcut.
Step 2:
In the VB editor, “Insert” a new module:
Write this line of code in the module to add a new sheet:
Sub Add_Sheet() Sheets.Add End Sub
Step 3:
Save the module (it may ask to name the module)
Step 4:
Run the code by pressing F5 or from this menu:
This should add a new sheet before “Sheet1” as this was our active sheet:
Adding a sheet with a specified name
To name a newly added sheet, use the Name as follows:
Sheets.Add.Name = “New Sheet Name”
In the example below, we will add a new sheet to our sample Workbook with the name “Product Information”.
See the VBA code and output below:
Sub Add_Sheet() Sheets.Add.Name = "Product Information" End Sub
Result Workbook:
As “Sheet3” became an active sheet, the “Product Information” sheet was added before it.
An example of adding named worksheet at the end of Workbook
Though there is no straightforward method to add a Worksheet at the end of an active Workbook, you may do this by getting the total count of existing sheets and then adding a new sheet at the end.
The example below shows how.
Sub Add_Sheet_End() 'Variable to get maximum sheet number Dim Max_sheet Max_sheet = Sheets.Count 'Adding after maximum sheet Sheets.Add After:=Sheets(Max_sheet) End Sub
Adding custom name sheet at the end
To add a custom-named sheet at the end, you may use this code and replace the name with your own. We will add “Product Information” sheet at the end.
VBA code:
Sub Add_Sheet_End() 'Variable to get maximum sheet number Dim Max_sheet Max_sheet = Sheets.Count 'Adding at the end with custom name Sheets.Add(After:=Sheets(Max_sheet)).Name = "Product Information" End Sub
Result:
Add a name sheet at the beginning
Creating a sheet as the first sheet while any of the sheets is active is also simple:
The code:
Sub Add_Sheet_Start() Sheets.Add(Before:=Sheets(1)).Name = "Sales Information" End Sub
Output:
Adding a Worksheet after a specific sheet
The following example shows adding a new Worksheet after a specified Worksheet. Suppose, we have the following sample Workbook sheets:
We want to add a new sheet after “Sheet10”.
Execute this code:
Sub Add_Sheet()
Sheets.Add After:=Sheets("Sheet10")
End Sub
Result:
You can see, “Sheet11” is added after “Sheet10”.
And to name the sheet as you want for the specified position:
Sub Add_Sheet_Name()
Sheets.Add(After:=Sheets("Sheet10")).Name = "Sales Sheet"
End Sub
Result:
You can see, we provided the name “Sales Sheet” and it is added after Sheet10 in our Workbook.
Adding before a specified sheet example
Just replace, “After” with “Before” in the above example, and a new sheet with the specified name will be added before. See the code and output:
VBA code:
Sub Add_Sheet_Name()
Sheets.Add(Before:=Sheets("Sheet10")).Name = "Employees Sheet"
End Sub
Result:
Adding multiple sheets after a specified position
Multiple sheets can also be added by a single line of code in VBA.
Just specify the count and position as shown in the example below.
We are adding 3 sheets after “Sheet13” in our sample Workbook:
VBA code:
Sub Add_Sheet_multiple()
Sheets.Add After:=Sheets("Sheet13"), Count:=3
End Sub
Result:
