A common Excel task when automating tasks in Excel using VBA is to add new sheets.
In VBA, this can easily be done using the Sheets.Add method.
In this article, I will cover various scenarios where you can use VBA to create a new sheet in Excel.
Sheets.Add is a method that adds a new sheet. You can use this to add a regular worksheet, a chart sheet, or a macro sheet.
When a new sheet is added, it automatically becomes the active sheet.
Below is the syntax of the Sheets.Add method:
Sheets.Add (Before, After, Count, Type)
- Before: Specify the sheet before which you want the new sheet to be added
- After: Specify the sheet after which you want the new sheet to be added
- Count: Specify the number of sheets you want to add
- Type: Specify what type of sheet you want to add (worksheet, chart sheet, or macro sheet). If not specified, it would add a worksheet.
Note that all of these arguments are optional, and if you just use Sheets.Add, it would create one new worksheet before the active sheet.
Now that you know about the ‘Sheets.Add‘ method, let’s see it in action in some practical examples.
Create One New Sheet
Below is the VBA code that will add one new sheet in the current workbook (in which you are running the code)
Sub CreateNewSheet() ' Create a new worksheet Sheets.Add End Sub
The new worksheet is always added to the left of the active sheet.
In the above code, I have used Sheets.Add method. You can also use Worksheets.Add
Create Multiple New Sheet
If you want to insert multiple new sheets, you can use the Sheets.Add method multiple times.
Below is the VBA code that will add three new sheets to the workbook.
Sub AddMultipleSheets() ' Create a new worksheet Sheets.Add ' Create the second new worksheet Sheets.Add ' Create the third new worksheet Sheets.Add End Sub
In the above, I have used the Sheets.Add method three times, so this adds three new worksheets.
You can also use a loop to add multiple new sheets, as shown below in the code that adds five new sheets.
Sub AddMultipleSheets() ' For loop to add five new sheets For i = 1 To 5 Sheets.Add Next i End Sub
The above code uses a For Next loop to add five new sheets. In case you want to run the loop any other number of times, you can change the value in the code.
Create a Sheet with a Specific Name
Sub CreateSheetWithSpecificName() ' Declare a variable to hold the new worksheet Dim ws As Worksheet ' Create a new sheet and assing it to the variable Set ws = Worksheets.Add ' Assign the name to the newly created sheet ws.Name = "NewName" End Sub
The above code first adds a new worksheet and assigns it to a variable called ws.
It then renames the sheet to “NewName”.
Add a New Sheet at the Beginning
Below is the VBA code that will add a new sheet at the beginning (so that it becomes the first sheet in the workbook)
Sub AddSheetAtBeginning() ' Create a new worksheet at the beginning of existing worksheets Sheets.Add Before:=Sheets(1) End Sub
In the above code, I have used the Before parameter of the Add method. I have used Before:=Sheets(1), so it adds the new sheet before the first sheet.
You can also tweak the code so that it adds a new sheet at the beginning and also renames it, using the name you specify in the code.
Below is the code that does this:
Sub AddSheetAtBeginning() ' Create a new worksheet at the beginning and rename it Sheets.Add(Before:=Sheets(1)).Name = "FirstSheet" End Sub
Running the code again will generate another sheet at the start, but Excel will automatically adjust the name (like “FirstSheet (2)”) to avoid any naming conflicts.
Also read: Clear Sheet Using VBA in Excel
Add a New Sheet at the End (After the Last Sheet)
Below is the VBA code that will add a new sheet at the end of all sheets in the workbook.
Sub AddSheetAtEnd() ' Create a new worksheet at the end of existing worksheets Sheets.Add After:=Sheets(Sheets.Count) End Sub
The above code uses Sheets.Count to get the count of all the worksheets in the workbook. It then uses this count number to add the sheet after this last worksheet.
You can also use the below (slightly modified) code to add the sheet at the end and give it the specified name.
Sub AddSheetAtEnd() ' Create a new worksheet at the end and rename it Sheets.Add(After:=Sheets(Sheets.Count)).Name = "LastSheet" End Sub
If you run the code more than once, Excel will automatically rename subsequent sheets (e.g., “LastSheet (2)”, “LastSheet (3)”, and so on) to prevent any naming conflicts.
Create a Sheet With the Name from a Cell
Below is the VBA code that creates a worksheets and names it using the value in cell A1 of the active sheet.
Sub CreateSheetWithNameFromCell() ' Declare a variable to hold the sheet name Dim sheetName As String ' Get the sheet name from cell A1 sheetName = ActiveSheet.Range("A1").Value ' Check if the cell is empty or not If sheetName = "" Then MsgBox "Cell A1 is empty. Please provide a name for the sheet.", vbExclamation Exit Sub End If ' Try to create a new worksheet with the specified name On Error Resume Next Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = sheetName If Err.Number <> 0 Then MsgBox "Error creating sheet. It might be because the name already exists or it's an invalid name.", vbExclamation End If On Error GoTo 0 End Sub
In the above code, I have used the variable sheetname to store the value in cell A1. This variable is then used to assign the name to the newly added sheet.
I have also placed some checks and balances in place in this code to handle potential errors.
In case the cell is empty or it has a name that can’t be used for a worsheet, the code will show an error and stop.
Add Sheet Before Or After a Specific Sheet
Below is the VBA code that adds a new sheet named “Summary” before the sheet named “Sales”
Sub AddSheetBeforeSpecificSheet() Worksheets.Add(Before:=Worksheets("Sales")).Name = "Summary" End Sub
While this code works, it doesn’t check whether the ‘Sales’ sheet exists or not.
Below is a more robust code that checks for the sheet’s existence first and then adds a new sheet after the Sales sheet.
Sub AddSheetBeforeSpecificSheet() ' Declare a variable to hold the target sheet name Dim targetSheetName As String targetSheetName = "Sales" ' Check if the target sheet exists Dim sheetExists As Boolean sheetExists = False Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets If ws.Name = targetSheetName Then sheetExists = True Exit For End If Next ws ' If the target sheet exists, add a new sheet before it If sheetExists Then Worksheets.Add(Before:=Worksheets(targetSheetName)).Name = "Summary" Else MsgBox targetSheetName & " not found. Please check the sheet name.", vbExclamation End If End Sub
Add Sheets from Values in a List/Range
Sometimes, you may want to add new sheets in bulk and assign them names based on the values in a list in the worksheet.
Below, I have some values in the range A1:A10, and I want to insert ten new sheets where the names should be taken from this range.
Below is the VBA code that would do this.
Sub AddSheetsFromList() ' Declare variables Dim rng As Range Dim cell As Range Dim sheetName As String ' Set the range containing the list of sheet names Set rng = ActiveSheet.Range("A1:A10") ' Loop through each cell in the range For Each cell In rng sheetName = Trim(cell.Value) ' Check if the cell is not empty and a sheet with the same name doesn't exist If sheetName <> "" Then ' Add the new worksheet with the specified name Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = sheetName End If Next cell End Sub
The above code loops through each cell in the range and then adds a new sheet for each of these names at the end. If you have a blank cell in the specified range, it will be ignored.
Add a Chart Sheet
So far, we have been talking about worksheets in Excel. Sheets.Add method, by default, adds a worksheet.
But you can also use it to add a chart sheet (which is a sheet dedicated to holding and displaying a single chart/graph).
Below is the VBA code to add a chart sheet.
Sub AddChartSheet() ' This line creates a new chart sheet Sheets.Add Type:=xlChart End Sub
Create a New Sheet and Copy Data from Another Sheet
The below VBA code will add a new sheet, copy all the cells from a specified sheet (Sheet1), and then paste it into the newly added sheet.
Sub CreateSheetAndCopyData() Dim newWs As Worksheet ' Create a new worksheet and assign to varaible Set newWs = ThisWorkbook.Sheets.Add ' Give the new worksheet a name newWs.Name = "NewSheet" ' Copy data from existing worksheet named "SourceSheet" to the new worksheet ThisWorkbook.Sheets("SourceSheet").Cells.Copy Destination:=newWs.Cells(1, 1) End Sub
When using this code, ensure you have a sheet named “SourceSheet” in the workbook, or replace “SourceSheet” in the code with the name of your source sheet.
Create a New Sheet in Another Workbook
You can also add new sheets to other workbooks that are open already. In case you want to create a new sheet in a closed workbook, then you need to first open that closed workbook and then add a new sheet to it.
Let’s see both of these examples.
VBA Code to Create Sheet in Already Open Workbook
Below is the code that will create a new sheet in an open workbook named Example.xlsx, and give this new worksheet a name (NewSheet)
Sub CreateSheetInOpenWorkbook() ' Declare the variables Dim targetWbName As String Dim newWs As Worksheet ' Set vairable to hold the name of the open workbook targetWbName = "Example.xlsx" ' Set tragetWb variable to the open workbook object Set targetWb = Workbooks(targetWbName) ' Add new sheet and assign it to the newWs vairable Set newWs = targetWb.Sheets.Add ' Give a Name to the new worksheet newWs.Name = "NewSheet" End Sub
This is the basic code you can use and then modify accordingly. For example, you can put some checks to make sure that the workbook is open and the sheet with the same name does not exists.
VBA Code to Create Sheet in a Closed Workbook
Below VBA code will open the workbook named Example.xlsx, add a new sheet to it, and then close it.
While this alone may not be useful in practical scenario, you can extend this code and use this to open a closed workbook, add a new sheet, and then copy data from some other sheet to this newly created sheet, and then close it. This would certainly be immensely helpful for some people.
Sub CreateSheetInClosedWorkbook() ' Declare the variables Dim targetWbPath As String Dim targetWb As Workbook Dim newWs As Worksheet ' Set variable to hold the path of the closed workbook targetWbPath = "C:\Users\sumit\Downloads\Example.xlsx" 'Replace with the path to your closed workbook ' Open the target workbook and set targetWb variable to the workbook object Set targetWb = Workbooks.Open(targetWbPath) ' Add new sheet and assign it to the newWs variable Set newWs = targetWb.Sheets.Add ' Give a Name to the new worksheet newWs.Name = "NewSheet" ' Save and close the workbook targetWb.Save targetWb.Close End Sub
While this code as is may not be useful in practical scenario, you can extend this code and use this to open a closed workbook, add a new sheet, and then copy data from some other sheet to this newly created sheet, and then close it.
This would certainly be immensely helpful for some people.
Remember to change the workbook name and the file path based on your requirements.
Create a New Sheet from Template
If you’re trying to create a new sheet in the same workbook using a template sheet (an existing sheet within that workbook acting as the template), you can do this with the code below:
Sub CreateSheetFromTemplate() ' Declare the Workbook and Worksheet variables Dim Wb As Workbook Dim Ws As Worksheet ' Set the current workbook to Wb Set Wb = ThisWorkbook ' Check if a template sheet named "Template" exists On Error Resume Next Set Ws = Wb.Worksheets("Template") On Error GoTo 0 ' If the template does not exist, exit the subroutine If Ws Is Nothing Then MsgBox "Template sheet not found!", vbExclamation Exit Sub End If ' Copy the template sheet to the end and rename it Ws.Copy After:=Wb.Sheets(Wb.Sheets.Count) Wb.Worksheets(Wb.Sheets.Count).Name = "NewSheet" End Sub
This code starts by checking if there’s a worksheet named “Template” in the current workbook.
If it finds one, it creates a copy of that template and places it at the end of all worksheets.
It then renames this new sheet to “NewSheet”. If the template does not exist, a message box pops up notifying the user.
You can change the sheet name in the code accordingly,
Create a New Sheet if It Doesn’t Exist
Here’s a VBA code that will ask the user for a sheet name and then check if that sheet already exists in the workbook. If it does not exist, it will add one with the given name, else it will show you a message box.
Sub CreateSheetIfNotExists() ' Declare the Workbook and Worksheet variables Dim Wb As Workbook Dim Ws As Worksheet Dim SheetName As String ' Set the current workbook to Wb Set Wb = ThisWorkbook ' Ask the user to input the desired name for the new sheet SheetName = InputBox("Enter the desired name for the new sheet:") ' If the input is empty, exit the subroutine If SheetName = "" Then Exit Sub ' Check if the specified sheet name already exists On Error Resume Next Set Ws = Wb.Worksheets(SheetName) On Error GoTo 0 ' If the sheet doesn't exist, create a new one with the specified name If Ws Is Nothing Then Wb.Worksheets.Add(After:=Wb.Worksheets(Wb.Worksheets.Count)).Name = SheetName Else MsgBox "A sheet with the name '" & SheetName & "' already exists!", vbExclamation End If End Sub
Remember that the changes done by VBA can not be reversed. So it’s essential to ensure you don’t accidentally overwrite or duplicate sheets, especially in workbooks with many sheets.
This VBA code helps prevent such mishaps by checking for the existence of a sheet before trying to create one with the same name.
Other Excel VBA articles you may also like: