Excel VBA: Add a New Worksheet with a Specific Name

VBA & macros · Updated November 19, 2026

To add a worksheet and give it a name with Excel VBA, add the sheet, then set its Name property. This example creates a sheet called Summary at the end of the worksheets in the workbook that contains the macro:

Sub AddNamedSheet()
    Dim newSheet As Worksheet

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

    ' Give the new worksheet its name.
    newSheet.Name = "Summary"
End Sub

Replace "Summary" with the name you want. The name must follow Excel’s worksheet naming rules and must not already be in use. Because the simple macro stops with an error if that name exists, a later example shows how to check first.

How the VBA statement works

Worksheets.Add creates a worksheet, while .Name assigns its tab name. The Set statement stores a reference to the new worksheet in the variable newSheet, so the next line can rename that exact sheet rather than relying on whichever sheet happens to be active.

The argument After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count) tells Excel where to put the new sheet: after the last worksheet in the workbook containing the VBA code. Worksheets.Count gives the number of worksheets, and using that number as an index identifies the last one. If you leave out Before and After, Excel generally inserts the new worksheet before the active sheet instead.

ThisWorkbook means the workbook that stores the macro. This is usually the right choice when the code is saved in the workbook you want to change. ActiveWorkbook, by contrast, means whichever workbook is currently active in Excel. That may be different—for example, if the code is stored in the Personal Macro Workbook—so use it only when you deliberately want the macro to work on the active workbook.

Choose where the new sheet goes

Excel VBA allows you to position a new worksheet before or after an existing sheet. For example, to put a new worksheet before a sheet named Data, use this pattern:

Sub AddSheetBeforeData()
    Dim newSheet As Worksheet

    ' Add a worksheet immediately before the Data worksheet.
    Set newSheet = ThisWorkbook.Worksheets.Add( _
        Before:=ThisWorkbook.Worksheets("Data"))

    newSheet.Name = "Summary"
End Sub

The referenced sheet must exist. If it does not, VBA raises an error when it tries to add the worksheet. You can also use After:=ThisWorkbook.Worksheets("Data") to place the new sheet after Data. A workbook can contain chart sheets as well as worksheets; the Worksheets collection refers only to worksheets, whereas the broader Sheets collection includes both kinds of sheets.

For a routine that simply adds a worksheet at the end of the worksheet tabs, the first example is usually easier to maintain than a fixed position. If users can rename or move sheets, refer to a known sheet by its current name only when that placement is important to the task.

Worksheet name rules to know

Excel limits a worksheet name to 31 characters. Names must be unique within the workbook without regard to capitalization, so Summary and summary count as the same name. A name cannot be blank or contain any of these characters: colon, backslash, forward slash, question mark, asterisk, or square brackets. It also cannot begin or end with an apostrophe.

These restrictions apply when a name is assigned in VBA just as they do when it is entered on a sheet tab. If a proposed name breaks a rule, the line assigning .Name produces a run-time error. A name that is valid in one workbook may still fail in another if that name is already taken there.

When names come from cell values or user input, trim unwanted spaces and validate the result before assigning it. Do not assume that a name is safe just because it looks ordinary: it may be too long, contain a prohibited character, or duplicate a sheet name. For predictable automation, define a naming convention in advance, such as a fixed prefix followed by a date or department label.

Avoid errors when the name already exists

A common issue is running a macro more than once. The first run creates Summary; a second attempt to create another sheet with exactly that name fails when VBA tries to rename the new sheet. One option is to stop politely if any sheet already has the target name:

Sub AddSummarySheetIfMissing()
    Dim targetName As String
    Dim sh As Object
    Dim newSheet As Worksheet

    targetName = "Summary"

    ' Check worksheets and chart sheets for a matching name.
    For Each sh In ThisWorkbook.Sheets
        If StrComp(sh.Name, targetName, vbTextCompare) = 0 Then
            MsgBox "A sheet named " & targetName & " already exists."
            Exit Sub
        End If
    Next sh

    ' Create and name the worksheet only when the name is available.
    Set newSheet = ThisWorkbook.Worksheets.Add( _
        After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
    newSheet.Name = targetName
End Sub

This check includes both worksheets and chart sheets because it loops through ThisWorkbook.Sheets. StrComp with vbTextCompare compares names without treating uppercase and lowercase as different. The code does not silently rename, delete, or replace an existing sheet; it displays a message and exits. That is safer when an existing tab may contain user data.

If you want each run to create a uniquely named sheet instead, write logic to select an unused name, such as Summary 2 or Summary 3. Make sure the generated name stays within the 31-character limit and check it against all existing sheets before assigning it. Avoid deleting an existing sheet merely to make room unless deletion is explicitly part of the intended workflow.

Run the macro in Excel for Microsoft 365

VBA macros run in the desktop version of Excel, not Excel for the web. In Excel for Microsoft 365 on Windows, open the workbook, press Alt+F11 to open the Visual Basic Editor, then choose Insert > Module. Paste the procedure into the module, return to Excel, and run it with Alt+F8: select the macro name and choose Run. You can also use the Developer tab’s Visual Basic and Macros commands. If Developer is not visible, enable the Developer tab in Excel Options.

On a Mac, VBA is available in desktop Excel, although keyboard shortcuts can vary with the keyboard and macOS settings. Use the Developer tab and choose Visual Basic or Macros if a shortcut such as Option+F11 or Option+F8 is unavailable. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run its VBA macros.

Save a workbook containing VBA in the Excel Macro-Enabled Workbook format (.xlsm) if you want to preserve the code in that file. A standard .xlsx workbook does not store VBA macros; Excel may warn that the code will be removed if you save in that format. Only enable macros from sources you trust. Save a copy of the workbook before running a macro: VBA actions cannot be undone with Ctrl+Z.

Adapting the macro for repeated tasks

For a one-time task, a fixed name such as Summary is clear and easy to read. In a reusable macro, the name can be assigned from a variable, provided the value is valid and available. For example, set targetName to a value collected from a cell or another part of the procedure, check it, then assign newSheet.Name = targetName. Keeping the name in one variable also makes it easier to update the macro without changing several separate lines.

You can prepare the new worksheet after naming it. For instance, you might put a heading in cell A1 with newSheet.Range("A1").Value = "Monthly Summary", or copy a template worksheet and rename the copy. Copying a template is different from adding a blank worksheet: it carries over the template’s formatting and content. In either case, hold a reference to the resulting worksheet and use that reference for subsequent changes. This avoids accidentally writing to the active sheet if the user clicks elsewhere or Excel changes the active tab.

Decide what should happen when the target name is taken. The macro can stop with a message, use a new unique name, or intentionally update an existing sheet. Those choices have different consequences. Updating an existing sheet may overwrite content, while creating a numbered alternative may leave several reports in the workbook. Make the behavior explicit rather than relying on an error or an unexpected sheet selection.

Common problems and practical checks

  • Run-time error while renaming: Check that the name is no longer than 31 characters, contains no prohibited characters, and is not already used by a worksheet or chart sheet.
  • The wrong workbook changes: Confirm whether the code should refer to ThisWorkbook or ActiveWorkbook. Use a fully qualified workbook reference instead of relying on the active window.
  • VBA cannot add a sheet: Check whether the workbook structure is protected. A protected workbook structure can prevent adding, deleting, moving, or renaming sheets. Remove protection only if you are authorized and have the password or other required access.
  • The macro is missing after saving: Save as .xlsm rather than .xlsx to retain VBA code in the workbook.
  • The code runs but the tab seems misplaced: Check the Before or After argument. The active sheet is not necessarily the last worksheet.
  • Excel says macros are disabled: Use a trusted workbook and follow your organization’s security policy. Do not lower macro security settings just to run code from an unknown file.

These checks help separate an issue with the VBA syntax from a workbook condition. If the error occurs on the Add line, inspect protection and the target workbook. If it occurs on the Name line, inspect the name rules and duplicates first.

Frequently Asked Questions

What is the simplest VBA code to add a sheet named Sheet1?
Declare a worksheet variable, use Set newSheet = ThisWorkbook.Worksheets.Add, and then assign newSheet.Name = "Sheet1". If Sheet1 already exists, the rename will fail, so check for a duplicate when the macro may run repeatedly.

How do I add a worksheet at the end of the workbook?
Use After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count) in the Worksheets.Add call. This places the new worksheet after the last worksheet, rather than relying on which tab is active.

Can VBA add a sheet only if it does not already exist?
Yes. Loop through ThisWorkbook.Sheets, compare each sheet’s Name with the target name, and exit or take another action when a match is found. Check all sheets if a chart sheet could have the same name.

Can I run this VBA code in Excel Online?
No. Excel for the web does not run VBA macros. Open the macro-enabled workbook in the desktop Excel application to edit or run its VBA code.

Why does VBA say the worksheet name is invalid?
The name may be blank, over 31 characters, already in use, or contain a forbidden character such as / or :. Check for leading or trailing apostrophes as well, then try a valid, unused name.

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.