Add Worksheets

Using VBA allows you to automate operations that you would otherwise perform manually in Excel. Among these, adding worksheets is one of the most basic yet useful scripts.

This article provides a detailed explanation of how to add worksheets using VBA, with practical examples.

How to Add Worksheets Using VBA

Basic Code for Adding a Worksheet

Below is the most basic code for adding a new worksheet using VBA.

Private Sub AddWorksheet()
    Worksheets.Add
End Sub

By executing this code, a new worksheet will be added to the Excel file where the VBA code is written.

How to Use the Worksheets.Add Method

Naming the Worksheet

When you execute the Worksheets.Add method, it returns the added worksheet object as its return value. You can use this object to name the new worksheet.

To name a new worksheet, use the following code:

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

Executing this script will add a worksheet named “NewSheet”.

Also, by specifying the name as a function argument as shown below, you can create the worksheet and set its name simultaneously:

Private Sub AddNamedWorksheet(ByVal name As String)
  Dim ws As Worksheet
  Set ws = Worksheets.Add
  ws.Name = name
End Sub

Adding a Worksheet at a Specified Position

To add a new worksheet at a specific position, use the following code:

Private Sub AddWorksheetAtPosition()
    Dim ws As Worksheet
    Set ws = Worksheets.Add(After:=Worksheets(1))
    ws.Name = "AddedAfter"
End Sub

Executing this script will add the worksheet after the first worksheet.

Adding Multiple Worksheets

To add multiple worksheets at once, use a loop.

Private Sub AddMultipleWorksheets()
    Dim i As Integer
    For i = 1 To 5
        Worksheets.Add.Name = "Sheet" & i
    Next i
End Sub

Executing this script will add five worksheets from “Sheet1” to “Sheet5”.

Practical Examples of Adding Worksheets

Automatically Generating a Database

For example, to automatically generate a worksheet with specific data:

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

    '_Add_header_row
    ws.Cells(1, 1).Value = "ID"
    ws.Cells(1, 2).Value = "Name"
    ws.Cells(1, 3).Value = "Age"

    '_Add_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 Integer
    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

Executing this script will add a worksheet named “DataSheet” and automatically populate it with header and data rows.

Adding a Worksheet Based on Conditions

For example, adding a worksheet based on a specific condition (e.g., when a specific cell has a value):

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

This script adds a worksheet named “ConditionalSheet” if the value in cell A1 is “Add”.

Troubleshooting

Worksheet Name Duplication Error

Attempting to add a worksheet with the same name as an existing worksheet will result in an error. To avoid this problem, add error handling.

Private Sub AddUniqueWorksheet()
    On Error Resume Next
    Dim ws As Worksheet
    Set ws = Worksheets.Add
    ws.Name = "UniqueSheet"

    If Err.Number <> 0 Then
        MsgBox "A_sheet_with_the_same_name_already_exists"
        Err.Clear
    End If
End Sub

This script displays an error message if a duplicate name exists.

Worksheet Added at Wrong Position

If the worksheet is not added at the specified position, re-check your code and verify that the position is specified correctly. For example, confirm that the Before or After options are used correctly.

Summary

We have provided a detailed explanation of how to add worksheets using VBA, from basic usage to advanced examples. Using these scripts, you can make your Excel work even more efficient.