Excel VBA: Add a New Sheet at the End and Name It Safely

VBA & macros · Updated November 19, 2026

To add a worksheet after the workbook’s last tab and name it in Excel VBA, use Worksheets.Add with the last item in the workbook’s Sheets collection as the After position, then set the new worksheet’s Name. The macro below creates a sheet called Summary and stops first if that name is already in use.

Save a copy of the workbook before running any macro: VBA changes cannot be undone with Ctrl+Z.

Sub AddSummaryAtEnd()
    Dim wb As Workbook
    Dim addedSheet As Worksheet
    Dim existingSheet As Object

    Set wb = ThisWorkbook

    ' Do not create a duplicate sheet name.
    Set existingSheet = Nothing
    On Error Resume Next
    Set existingSheet = wb.Sheets("Summary")
    On Error GoTo 0

    If Not existingSheet Is Nothing Then
        MsgBox "A sheet named Summary already exists."
        Exit Sub
    End If

    ' Add the worksheet after the workbook's last sheet tab.
    Set addedSheet = wb.Worksheets.Add(After:=wb.Sheets(wb.Sheets.Count))
    addedSheet.Name = "Summary"
End Sub

What “at the end” means in VBA

Excel displays worksheets and chart sheets as tabs in a workbook. VBA has a Worksheets collection for worksheets and a broader Sheets collection for all sheet types, including chart sheets. The code uses the last item in wb.Sheets as the insertion point. This places the new worksheet after the last visible sheet tab, even if that last tab is a chart sheet.

Using After:= is important because Worksheets.Add otherwise adds the new worksheet before the active sheet by default. That default may put it somewhere unexpected. In the example, wb.Sheets.Count returns the number of sheet tabs, and the item at that index is the last one. The new sheet is inserted immediately after it.

The code then stores the new worksheet in addedSheet and assigns its name. Keeping a reference to the worksheet makes it clear which sheet is being renamed; it avoids relying on whichever sheet happens to be active later in a longer macro.

Choose the workbook the macro should change

The example sets wb to ThisWorkbook, meaning the workbook that contains the VBA code. This is usually the right choice when the macro is saved inside the workbook it should edit. It is more predictable than relying on whichever workbook Excel currently considers active.

If the macro is stored in a separate macro workbook, such as PERSONAL.XLSB, ThisWorkbook refers to that container, not necessarily the workbook you are looking at. If you intentionally want to change the currently active workbook, replace the assignment with Set wb = ActiveWorkbook. Be careful: the active workbook can change if a macro opens or switches between workbooks. Check the target workbook before running the procedure.

In a larger macro, you can also pass a workbook into a procedure rather than choose one implicitly. That approach is useful when a routine must operate on a specific workbook selected or opened earlier in the code, but it is not necessary for a simple one-workbook task.

Set up and run the macro

  1. In Excel for Microsoft 365 on Windows, open the workbook and press Alt+F11 to open the Visual Basic Editor. On a Mac, use Option+F11; depending on the keyboard, you may also need the Fn key.
  2. In the editor, choose Insert > Module. Paste the procedure into the module, then return to Excel.
  3. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if it is not already saved in a macro-capable format. A regular .xlsx file cannot store VBA code.
  4. Run the procedure from the Visual Basic Editor, or in Excel choose Developer > Macros, select AddSummaryAtEnd, and choose Run. If the Developer tab is hidden, enable it in Excel’s ribbon settings.

Excel for the web can open a macro-enabled workbook, but it cannot run or edit VBA macros. Open the file in the desktop application to use this procedure. On Mac, the Visual Basic Editor and VBA are available in desktop Excel, although keyboard shortcuts and some workbook features can differ from Windows.

Use a different sheet name

To choose another name, replace "Summary" in both places in the example: the duplicate-name check and the assignment to addedSheet.Name. For example, use "April Report" for a sheet with that tab name. The duplicate check and assigned name must match; otherwise the macro checks for one name but creates another.

Excel applies naming rules to worksheet tabs. A name must be unique within the workbook, cannot be blank, and can contain no more than 31 characters. It cannot include a colon, backslash, slash, question mark, asterisk, or square bracket, and it cannot begin or end with an apostrophe. Names that differ only in letter case are not distinct for this purpose. For example, Summary and SUMMARY conflict.

These restrictions can matter when the name comes from a cell or a variable. If your code builds a name from a date, customer, or report title, validate its length and remove forbidden characters before assigning it. Otherwise, the macro may create the sheet successfully but fail when it tries to set the invalid name. Also consider whether truncating a long name could produce a duplicate of an existing tab.

Why check for a duplicate first?

Sheet names must be unique across the workbook’s sheets, including chart sheets. If a sheet named Summary already exists, assigning that name to a newly added worksheet raises a run-time error. Without a check, the macro may have already inserted a blank worksheet by the time it stops, leaving an unexpected extra tab behind.

The example looks up wb.Sheets("Summary"). VBA raises an error when that item does not exist, so On Error Resume Next is used briefly for the lookup, then turned off immediately with On Error GoTo 0. If the lookup found an item, existingSheet is not Nothing and the macro exits before adding anything. This local use is different from leaving error suppression on for the whole macro, which can hide unrelated problems.

The current response to a duplicate is to show a message and stop. Another valid design is to choose a numbered name, such as Summary 2, but that requires a loop that checks each candidate before adding the sheet. Stopping is often safer for reports because silently choosing a new name may cause later code or users to refer to the wrong tab.

Common errors and how to fix them

  • “A sheet with that name already exists.” Check for an existing worksheet or chart sheet with the same name, or use a different valid name. The example avoids this error by checking before it adds a worksheet.
  • “The name is not valid.” Check the 31-character limit, prohibited characters, blank names, and leading or trailing apostrophes. If the name comes from a cell, inspect the value after any cleanup or truncation.
  • The macro adds the sheet to an unexpected workbook. Confirm whether ThisWorkbook or ActiveWorkbook is appropriate. The former is the code’s container; the latter is the workbook currently active in Excel.
  • Excel will not add a sheet. The workbook’s structure may be protected. A protected workbook structure can prevent adding, deleting, moving, or renaming sheets. Check Review > Protect Workbook and follow the workbook owner’s process for removing protection; do not attempt to bypass protection.
  • The macro does not appear to run. Confirm the file is saved as .xlsm, that you opened it in desktop Excel, and that the macro is permitted by your organization’s security settings. Do not enable macros in a file from an untrusted source simply to run this code.

Alternative placement and naming patterns

If “at the end” means after the last worksheet only, rather than after the last tab of any type, you can use the last item in wb.Worksheets as the insertion point. That distinction matters only when the workbook contains chart sheets. The main example uses wb.Sheets because readers commonly mean the end of the complete tab bar.

To add the sheet before a particular worksheet instead, use that worksheet as the Before argument. For example, a report sheet could be inserted immediately before a worksheet named Archive. To add it at the beginning, use the first sheet as the Before target. These choices control position; the separate Name assignment controls the tab label.

For repeatable automation, keep the target workbook and intended sheet name in variables, check for conflicts, and handle any expected protection or invalid-name conditions explicitly. Avoid depending on ActiveSheet when the macro needs a precise insertion position: the active tab is a user-interface state, not a dependable definition of “last.”

Frequently Asked Questions

What is the simplest VBA command to add a sheet at the end?
Use Worksheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)). Store the returned worksheet in a variable if you need to name it or work with it afterward.

How do I add and name a sheet in one macro?
Assign the result of Worksheets.Add to a worksheet variable, then set that variable’s Name property, as in addedSheet.Name = "Summary". Check for a name conflict first to prevent a run-time error.

Does the new worksheet become active?
Excel normally activates a worksheet when it is added. The example does not rely on that behavior: it refers to the new sheet through addedSheet, which is more reliable if the macro later selects or activates other sheets.

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

Why use Sheets.Count instead of Worksheets.Count?
Sheets.Count includes worksheet and chart-sheet tabs, so it identifies the end of the whole tab bar. Worksheets.Count counts only worksheets and may not identify the last tab if a chart sheet follows them.

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.