Add Worksheets

To add a worksheet in VBA, use the Worksheets.Add method.

You can add a sheet with no arguments at all, but in practice you also need to think about naming the sheet, adding it at the end, and what happens when a sheet with the same name already exists.

This article explains the basic code, specifying names and positions, adding multiple sheets, and handling errors, with code examples.

Basic code for adding a worksheet

Private Sub AddWorksheet()
    ThisWorkbook.Worksheets.Add
End Sub

Running this adds a new worksheet before the active sheet in ThisWorkbook (the workbook containing the macro), and the new sheet becomes the active sheet.

Arguments of the Worksheets.Add method

Add takes the following arguments, all optional.

Before and After cannot be specified together. If both are omitted, the sheet is added before the active sheet.

Naming the worksheet

Worksheets.Add returns the added worksheet, so you can set its name right away.

Private Sub AddNamedWorksheet()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets.Add
    ws.Name = "New Sheet"
End Sub

Accepting the name as an argument makes it easier to reuse.

Private Sub AddNamedWorksheetByArg(ByVal sheetName As String)
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets.Add
    ws.Name = sheetName
End Sub

Adding a worksheet at a specific position

Use Before or After to specify where the sheet goes.

Private Sub AddWorksheetAtPosition()
    Dim ws As Worksheet

    ' Add at the beginning
    Set ws = ThisWorkbook.Worksheets.Add(Before:=ThisWorkbook.Worksheets(1))
    ws.Name = "First"

    ' Add after the first sheet
    Set ws = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(1))
    ws.Name = "AfterFirst"

    ' Add at the end
    Set ws = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
    ws.Name = "Last"
End Sub

To add at the end, use Worksheets.Count (the number of sheets) to refer to the last sheet.

Adding multiple worksheets

To add several named sheets, loop and add them one by one.

Private Sub AddMultipleWorksheets()
    Dim i As Long
    Dim ws As Worksheet
    For i = 1 To 5
        Set ws = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        ws.Name = "Sheet" & i
    Next i
End Sub

This adds five worksheets, “Sheet1” through “Sheet5”, in order at the end. If you do not need names, Worksheets.Add Count:=5 adds them in one call.

Practical examples

Creating a sheet pre-filled with data

This example generates a sheet with a header row and data rows.

Private Sub AddAndFillWorksheet()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets.Add
    ws.Name = "DataSheet"

    ' Add the header row
    ws.Cells(1, 1).Value = "ID"
    ws.Cells(1, 2).Value = "Name"
    ws.Cells(1, 3).Value = "Age"

    ' Add the data rows
    Dim data As Variant
    data = Array( _
        Array(1, "Taro Tanaka", 30), _
        Array(2, "Hanako Yamada", 25), _
        Array(3, "Jiro Sato", 35) _
    )

    Dim i As Long
    For i = 0 To UBound(data)
        ws.Cells(i + 2, 1).Value = data(i)(0)
        ws.Cells(i + 2, 2).Value = data(i)(1)
        ws.Cells(i + 2, 3).Value = data(i)(2)
    Next i
End Sub

A worksheet named “DataSheet” is added, with the header row and data rows filled in.

Adding a worksheet based on a condition

This example adds a sheet only when cell A1 of the active sheet contains “Add”.

Private Sub AddWorksheetIfConditionMet()
    If ActiveSheet.Range("A1").Value = "Add" Then
        Dim ws As Worksheet
        Set ws = ThisWorkbook.Worksheets.Add
        ws.Name = "ConditionalSheet"
    End If
End Sub

Troubleshooting

Duplicate worksheet name error

Setting a name that an existing sheet already uses raises run-time error 1004. Worse, the sheet created by Add is left behind with its default name.

To avoid this, check for a sheet with the same name before adding.

Private Function SheetExists(ByVal sheetName As String) As Boolean
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If StrComp(ws.Name, sheetName, vbTextCompare) = 0 Then
            SheetExists = True
            Exit Function
        End If
    Next ws
End Function

Private Sub AddUniqueWorksheet()
    Const SHEET_NAME As String = "UniqueSheet"

    If SheetExists(SHEET_NAME) Then
        MsgBox "A sheet with the same name already exists"
        Exit Sub
    End If

    ThisWorkbook.Worksheets.Add.Name = SHEET_NAME
End Sub

Sheet names are case-insensitive, so the comparison uses StrComp with vbTextCompare.

Cannot add a worksheet

If the workbook structure is protected, sheets cannot be added. Remove the protection with ThisWorkbook.Unprotect and then add the sheet.

The sheet is added in the wrong position

If you omit Before / After, the sheet is added before the active sheet. When position matters, always specify Before or After.

Summary

• Add a worksheet with Worksheets.Add, and specify the target workbook explicitly, e.g. with ThisWorkbook
• Use Before / After for position and Count for the number of sheets; by default the sheet goes before the active sheet
• Sheet names must be 31 characters or fewer, cannot contain : \ / ? * [ ], and must be unique
• Prevent duplicate-name errors by checking for an existing sheet before adding