Add Worksheet VBA: Excel Methods and Examples

To add a worksheet with VBA in Excel, use Worksheets.Add, then assign the new sheet a name. For example, a macro can add a tab after the workbook’s last worksheet and name it “Summary”; save a copy first, because running a VBA macro cannot be undone with Ctrl+Z.

What “add worksheet” means in VBA

In Excel VBA, a worksheet is one tab of cells in a workbook. The Worksheets.Add method creates a worksheet; it is different from adding a chart sheet, which is also possible through the broader Sheets collection. Using Worksheets is usually the clearer choice when the new tab needs rows, columns, formulas, or cell formatting.

If you do not specify where to insert the new sheet, Excel places it before the active sheet. You can instead specify a worksheet before or after which the new tab should appear. The method can also add more than one sheet, though most macros add one at a time so they can name and configure each sheet deliberately.

A worksheet-adding macro is useful when a workbook is built repeatedly: for example, when a monthly report needs a fresh summary tab, a template needs a new input sheet, or a process creates a separate worksheet for each department. The macro only adds the sheet; it does not automatically copy data, apply a template, or make the new worksheet part of a reporting process unless you write those steps too.

Basic VBA example: add and name a worksheet

In the following example, the workbook containing the macro is the workbook that receives the new worksheet. The new tab is inserted after the last worksheet, and its name is set to “Summary.”

Sub AddSummaryWorksheet()
    Dim ws As Worksheet

    ' Add a worksheet after the last worksheet in this workbook.
    Set ws = ThisWorkbook.Worksheets.Add( _
        After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))

    ' Give the new worksheet a name.
    ws.Name = "Summary"
End Sub

Before running this example, save a copy of the workbook. VBA changes cannot be reversed with Ctrl+Z. Also check that a worksheet named Summary does not already exist: Excel does not allow two worksheets in the same workbook to have the same name, and this simple example will produce an error when it tries to assign a duplicate name. A safer version that checks first appears below.

The Set statement stores a reference to the worksheet returned by Add. That reference lets the macro rename the new tab and, if needed, continue by writing values or formatting cells on that specific sheet. For instance, after assigning the name, code could refer to ws.Range("A1") rather than relying on whichever sheet happens to be active.

Choose where the new tab appears

The optional Before and After arguments control the insertion point. Use one of them, not both. To insert a new worksheet before the first worksheet, use this pattern:

Sub AddWorksheetAtBeginning()
    Dim ws As Worksheet

    ' Insert before the first worksheet in this workbook.
    Set ws = ThisWorkbook.Worksheets.Add( _
        Before:=ThisWorkbook.Worksheets(1))

    ws.Name = "Instructions"
End Sub

To place a sheet after a particular tab, refer to that worksheet by name, such as After:=ThisWorkbook.Worksheets("Data"). That reference requires a worksheet named Data to exist. If the name is missing or has been changed, the macro stops with an error. For a workbook used by other people, check that the expected sheet exists before trying to insert next to it, or use a dependable position such as the end of the worksheet collection.

When adding multiple worksheets, the Count argument sets how many to create. For example, ThisWorkbook.Worksheets.Add After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count), Count:=3 adds three worksheets at the end. Excel assigns default names to the new tabs. If each needs a meaningful name, retain references to the sheets and name them individually, taking care that each chosen name is unique.

Avoid errors by checking for an existing name

A macro that may run more than once should usually check whether its target tab already exists. This version looks for a worksheet named Summary without regard to capitalization. If it finds one, it uses the existing sheet; if not, it adds and names a new one.

Sub AddSummaryIfMissing()
    Dim candidate As Worksheet
    Dim newSheet As Worksheet

    ' Look for a worksheet with the requested name.
    For Each candidate In ThisWorkbook.Worksheets
        If StrComp(candidate.Name, "Summary", vbTextCompare) = 0 Then
            Set newSheet = candidate
            Exit For
        End If
    Next candidate

    ' Add the worksheet only when it does not already exist.
    If newSheet Is Nothing Then
        Set newSheet = ThisWorkbook.Worksheets.Add( _
            After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        newSheet.Name = "Summary"
    End If
End Sub

This prevents a duplicate-name error, but it does not clear or rebuild an existing Summary sheet. That distinction matters: if the macro is meant to refresh a report, you need to decide what should happen to any old content. Reusing an existing worksheet preserves its data; deleting and recreating it can remove content and may break formulas, links, or references. Make that choice explicit rather than assuming that “add if missing” also refreshes a sheet.

Worksheet names have practical restrictions: they cannot be blank, cannot contain certain characters such as a colon or slash, and cannot exceed 31 characters. Names are not case-sensitive for uniqueness, so Summary and SUMMARY are not separate worksheet names. If a name comes from user input or a cell, validate it and handle possible conflicts before assigning it.

Use the intended workbook reference

The examples use ThisWorkbook, which means the workbook containing the VBA code. This is often the right choice for a macro stored in the workbook it modifies. By contrast, ActiveWorkbook means the workbook currently active in Excel. It may be a different file, particularly when a macro is stored in the Personal Macro Workbook or an add-in. Choosing the wrong reference can put the new tab in an unexpected workbook.

Likewise, avoid relying on ActiveSheet when the macro has a clear target. The active sheet changes when a user clicks another tab, and code can also change the active sheet. A direct reference such as ThisWorkbook.Worksheets("Data") makes the target clearer, provided the worksheet exists. For dependable macros, explicitly identify both the workbook and worksheet you intend to change.

Using Worksheets also means the code addresses worksheets rather than chart sheets. A workbook can contain both types of tabs. If you use Sheets.Count to determine a position but then work with Worksheets, chart sheets can make the numbering differ. Keep the collection consistent: use Worksheets.Count when positioning a new worksheet among worksheets.

Where to put and run the macro in Excel

In Excel for Microsoft 365 on Windows, open the VBA editor with Developer > Visual Basic, or press Alt+F11. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon, then select Developer. In the Visual Basic Editor, choose Insert > Module and paste a macro into the module. A standard module is suitable for a macro you want to run manually.

To run it, return to Excel and press Alt+F8, select the macro name, and choose Run. You can also use Developer > Macros. Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm) if it contains VBA; a normal .xlsx file does not preserve VBA macros. Keep a backup before running code that changes a workbook, since Ctrl+Z does not undo macro actions.

Excel for Mac supports VBA in the desktop application, but its keyboard layout and editor access can differ. Option+F11 is a common Mac shortcut for the Visual Basic Editor, though some keyboards require Fn as well. Excel for the web cannot create, edit, or run VBA macros; open the workbook in desktop Excel to use VBA. A workbook containing macros can be stored online, but the browser version will not execute its VBA.

Common problems and safer habits

Duplicate worksheet name: The new sheet may be created before the rename fails. Check for an existing tab first, as in the guarded example, and decide whether the macro should reuse or update it.
Missing reference sheet: Code using Worksheets("Data") fails if that tab is absent or renamed. Confirm the required worksheet exists before using it as an insertion point.
Wrong workbook: ActiveWorkbook can point to a different file than expected. Use ThisWorkbook when the macro should modify the file in which its code is stored.
Macro will not run: Confirm the workbook is saved in a macro-enabled format, that Excel permits the macro to run, and that the code is in a standard module if you plan to launch it through the Macros dialog.
Unexpected result after running: Test changes on a copy, especially if later code deletes sheets or overwrites cells. VBA changes are not undone by Ctrl+Z.

Macro security is important because VBA can change files and perform other actions. Run macros only when you trust their source and understand their purpose; do not lower security settings broadly just to make an unfamiliar file run. If a workbook is shared, tell recipients that it contains a macro and what the macro does. These precautions are useful even for a short sheet-adding routine, because real workbooks often grow to include more consequential code.

When VBA is the right choice

VBA makes sense when the worksheet must be created as one step in a repeatable desktop Excel workflow, or when other VBA code will populate and format the new tab. It is particularly useful for a button-driven process that prepares a recurring report, provided the macro is maintained and users can run it safely.

If the only task is to add one blank worksheet occasionally, clicking the plus button beside the sheet tabs or using Home > Insert > Insert Sheet may be simpler. For browser-based automation, Microsoft Office Scripts are a separate option in supported Microsoft 365 environments; they are not VBA, use a different programming model, and have different availability. Choose the method that matches where the workbook is used and who needs to run the process.

Frequently Asked Questions

What is the VBA code to add a worksheet? Use Set ws = ThisWorkbook.Worksheets.Add after declaring ws As Worksheet. You can then set ws.Name. Specify Before or After in the Add call when the tab needs a particular position.

How do I add a worksheet at the end with VBA? Set the After argument to the last worksheet: After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count). This places the added worksheet after the existing worksheets in the workbook.

Why does my add worksheet macro show a name error? A worksheet with that name may already exist, or the requested name may violate Excel’s naming restrictions. Check for an existing worksheet before assigning the name, and use a valid name of no more than 31 characters.

Can I run VBA to add a worksheet in Excel for the web? No. Excel for the web does not run VBA. Open the file in desktop Excel for Windows or Mac to run the macro. Office Scripts may be an alternative for some web workflows, but they are not VBA.

Can I undo a worksheet added by a macro with Ctrl+Z? No. Running VBA clears Excel’s normal undo history, so Ctrl+Z does not reverse the macro’s changes. Save a copy before running code that modifies a workbook.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.