VBA Code to Add a Worksheet in Excel: Safe Examples

VBA & macros · Updated November 18, 2026

To add a worksheet with VBA in Excel, use Worksheets.Add. For example, this macro adds a sheet after the workbook’s last worksheet and names it Summary:

Sub AddSummaryWorksheet()
    Dim ws As Worksheet

    ' Add a worksheet after the last existing worksheet.
    Set ws = ThisWorkbook.Worksheets.Add( _
        After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
    ws.Name = "Summary"
End Sub

Before running any macro, save a copy of the workbook: VBA changes cannot be undone with Ctrl+Z. The example uses ThisWorkbook, meaning the workbook that contains the code; that distinction matters when a macro is stored somewhere other than the workbook being edited.

What Worksheets.Add Does

Worksheets.Add creates a worksheet and returns a reference to the new sheet. If you omit its position arguments, Excel inserts the new worksheet before the active sheet. That default can be surprising in a macro, because the active sheet may differ depending on what the user clicked or how the procedure was started. Specifying Before or After makes placement predictable.

The method also accepts optional arguments such as Count and Type. Count controls how many sheets to add; if you do not provide it, one is added. For ordinary worksheets, leave Type out. The method can also create other sheet types, such as chart sheets, but those are not worksheets and do not belong to the Worksheets collection.

Assigning the result to a variable, as in Set ws = Worksheets.Add(...), lets the rest of the macro refer directly to the new worksheet. This is safer than relying on whichever sheet happens to be active after insertion. You can then name the sheet, write values to it, or format it through ws.

Choose Where the New Sheet Goes

Use After to put a worksheet after a known sheet, or Before to put it in front of one. To append a worksheet to the end, use the last worksheet in the workbook as the After target, as in the opening example. To place one at the beginning, use the first worksheet as the Before target:

Sub AddInputWorksheetFirst()
    Dim wb As Workbook
    Dim ws As Worksheet

    Set wb = ThisWorkbook
    ' Insert before the first worksheet in this workbook.
    Set ws = wb.Worksheets.Add(Before:=wb.Worksheets(1))
    ws.Name = "Inputs"
End Sub

You can target a named worksheet instead of the first or last one. The following example inserts a sheet immediately after a worksheet named Read Me:

Sub AddWorksheetAfterReadMe()
    Dim wb As Workbook
    Dim ws As Worksheet

    Set wb = ThisWorkbook
    ' Read Me must exist as a worksheet in this workbook.
    Set ws = wb.Worksheets.Add(After:=wb.Worksheets("Read Me"))
    ws.Name = "Details"
End Sub

If the named target does not exist, the code stops with an error when it tries to find it. In a procedure used by other people, check that the target sheet exists first or handle the error deliberately. Also note that Worksheets.Count counts worksheets, not every possible sheet tab. That is appropriate when adding after the last worksheet; use the broader Sheets collection when your logic specifically needs to account for chart sheets too.

Name the Worksheet and Avoid Duplicate Names

Excel requires sheet names to be unique within a workbook. A name cannot be blank, exceed 31 characters, or contain characters such as a colon, slash, backslash, question mark, asterisk, or square brackets. A fixed name such as Summary works until the macro runs a second time: the new sheet is created, but assigning the already-used name causes an error.

One practical approach is to add a numbered suffix whenever the base name is already in use. This complete example checks all sheet tabs, including chart sheets, before choosing a name:

Sub AddNumberedReportWorksheet()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim baseName As String
    Dim candidate As String
    Dim suffix As Long

    Set wb = ThisWorkbook
    baseName = "Monthly Report"
    candidate = baseName
    suffix = 2

    ' Find an unused name, including names of chart sheets.
    Do While SheetNameExists(wb, candidate)
        candidate = baseName & " (" & suffix & ")"
        suffix = suffix + 1
    Loop

    Set ws = wb.Worksheets.Add( _
        After:=wb.Worksheets(wb.Worksheets.Count))
    ws.Name = candidate
End Sub

Private Function SheetNameExists(ByVal wb As Workbook, _
                                 ByVal sheetName As String) As Boolean
    Dim sh As Object

    ' Compare without regard to capitalization.
    For Each sh In wb.Sheets
        If StrComp(sh.Name, sheetName, vbTextCompare) = 0 Then
            SheetNameExists = True
            Exit Function
        End If
    Next sh
End Function

On its first run, this names the sheet Monthly Report; on later runs it uses Monthly Report (2), Monthly Report (3), and so on. The base name in this example is short and contains no prohibited characters. If a name comes from a cell or user input, validate its length and characters before assigning it; a duplicate-name check alone does not make an invalid name acceptable.

Add Several Worksheets at Once

To add multiple worksheets, pass a number to the Count argument. Excel gives the new sheets default names, so a loop is useful if you want to rename each one. This example appends three worksheets and gives them distinct names:

Sub AddQuarterWorksheets()
    Dim wb As Workbook
    Dim i As Long
    Dim ws As Worksheet

    Set wb = ThisWorkbook
    ' Add three worksheets after the current last worksheet.
    wb.Worksheets.Add After:=wb.Worksheets(wb.Worksheets.Count), _
                      Count:=3

    For i = 1 To 3
        Set ws = wb.Worksheets(wb.Worksheets.Count - 3 + i)
        ws.Name = "Quarter " & i
    Next i
End Sub

The new worksheets are added together at the end, and the loop refers to those final three positions. This example assumes the names Quarter 1 through Quarter 3 are not already taken. If the macro may run repeatedly, use a naming strategy that checks for existing names, or remove and recreate the previous generated sheets only when that is safe for the workbook’s data.

Where to Put and How to Run the Macro

In Excel for Microsoft 365 on Windows, open the workbook and press Alt+F11 to open the Visual Basic Editor. In the editor, choose Insert > Module, paste a complete procedure into the module, and return to Excel. Run a macro from the editor by placing the cursor inside its procedure and pressing F5, or return to Excel and press Alt+F8, select the macro, and choose Run. If the Developer tab is visible, Developer > Visual Basic opens the editor and Developer > Macros opens the macro list.

If the Developer tab is not shown, enable it through File > Options > Customize Ribbon, then select Developer in the Main Tabs list. On Mac, use the Developer tab’s Visual Basic command if available; keyboard shortcuts can vary with the keyboard and function-key settings. Excel for the web can open and edit many macro-enabled workbooks, but it cannot create, edit, or run VBA macros. Use the desktop Excel application for these steps.

Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you want its code to remain in that file. Saving as a standard .xlsx file does not preserve VBA macros. If Excel displays a security warning, only enable macros when you trust the source and understand what the code will change. Do not change security settings to enable every macro indiscriminately.

Common Errors and How to Fix Them

  • Run-time error while naming the sheet: The name may already exist, be too long, or include a prohibited character. Check the name before assigning it, and keep generated names within Excel’s limits.
  • Subscript out of range: A referenced worksheet name may be misspelled or absent from the workbook object used by the code. Confirm the exact tab name and whether the macro should use ThisWorkbook or ActiveWorkbook.
  • Cannot add or move a sheet: The workbook’s structure may be protected. Review the workbook protection settings and, if appropriate, unprotect the workbook through Review > Protect Workbook before running the macro. Do not remove protection unless you are authorized to do so.
  • The sheet appears in the wrong file: ThisWorkbook refers to the workbook containing the VBA code. If the code is in a personal macro workbook or an add-in, that may not be the workbook you are viewing. Use ActiveWorkbook only when the active workbook is intentionally the target, and make sure it is the correct file before making changes.
  • The macro does not run: Confirm that the file is saved as .xlsm, that the macro is in a standard module, and that Excel has not blocked the code. A workbook opened in Excel for the web cannot run the VBA procedure.

Some errors can occur after the worksheet has already been inserted. For example, if the macro adds a sheet and then fails while naming it, an unnamed default worksheet may remain. Check the workbook after an interrupted run rather than repeatedly running the procedure without inspecting its state.

VBA Compared with Adding a Sheet Manually

For a one-time change, using the worksheet plus button beside the sheet tabs is usually quicker than writing a macro. You can also use Home > Insert > Insert Sheet in desktop Excel. VBA is useful when the task is repeated, when a worksheet must be created as part of a larger process, or when its position and name need to follow consistent rules.

A macro can also prepare the new sheet after inserting it—for example, by writing headings or setting a tab color. Those extra actions should be explicit in the code. Adding a worksheet does not automatically copy the layout or formulas from another sheet; if you need a template, copy an existing worksheet deliberately and account for its formulas, references, and name.

Frequently Asked Questions

What is the simplest VBA code to add a worksheet?
Use Worksheets.Add, such as Set ws = ThisWorkbook.Worksheets.Add, after declaring ws As Worksheet. With no position specified, Excel inserts the new worksheet before the active sheet.

How do I add a worksheet at the end of a workbook?
Set the After argument to the workbook’s last worksheet: Worksheets.Add After:=Worksheets(Worksheets.Count). Qualify both references with the intended workbook to avoid depending on whichever workbook is active.

How can I add a worksheet with a specific name?
Store the new worksheet returned by Worksheets.Add in a variable, then assign its Name property. Check first that the name is unique and follows Excel’s naming rules.

Can VBA add more than one worksheet?
Yes. Use the Count argument, for example Worksheets.Add Count:=3, and then refer to the newly created worksheets to rename or prepare them. Confirm that the names you assign do not already exist.

Can I run this worksheet macro in Excel for the web?
No. Excel for the web does not run VBA macros. Open the macro-enabled workbook in the desktop version of Excel for Windows or Mac to run the procedure.

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

Related

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.