Create New Sheet Using VBA in Excel (Sheets.Add)

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 Method

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)

Where:

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

Also read: How to Insert New Worksheet in Excel (Shortcuts)

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.

Also read: VBA Check IF Cell is Empty (ISEMPTY Function)

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.

Add Sheets from Values in a List 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.

Also read: VBA to Hide Sheets

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.

What is VBA in Excel?

Other Excel VBA articles you may also like:

Picture of Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.
Free-Excel-Tips-EBook-Sumit-Bansal-1.png

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free-Excel-Tips-EBook-Sumit-Bansal-1.png

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free Excel Tips EBook Sumit Bansal

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster