Excel VBA: Add a New Worksheet With a Specific Name

In Excel VBA, add a worksheet with Worksheets.Add, then set its Name property. For example, the key steps are Set ws = ThisWorkbook.Worksheets.Add and ws.Name = "Summary"; the complete macro below also shows how to choose the sheet’s position and avoid a duplicate name.

The basic VBA method

The Add method creates a worksheet, and the object it creates can be assigned to a worksheet variable. Setting that object’s Name property gives the new tab its desired label. Keeping these operations separate makes the code easier to understand and lets you refer to the new sheet later in the macro.

Sub AddSummarySheet()
    Dim ws As Worksheet

    ' Add a worksheet, then give it a name.
    Set ws = ThisWorkbook.Worksheets.Add
    ws.Name = "Summary"
End Sub

This short version adds the sheet at Excel’s default insertion position, usually before the active sheet. If a worksheet called Summary already exists in the workbook, the name assignment raises an error. The macro does not silently replace or rename the existing tab.

Before running any macro, save a copy of the workbook. VBA actions generally cannot be reversed with Ctrl+Z, so a backup is the simplest way to recover if a macro changes the wrong file or creates an unwanted sheet.

Choose where the new worksheet goes

You can control placement with the Before or After arguments of Worksheets.Add. For example, to place the new worksheet after the worksheet named Instructions, use this statement, assuming that worksheet exists in ThisWorkbook:

Set ws = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets("Instructions"))

To add a worksheet after the last worksheet in the workbook, use the worksheet collection’s count:

Set ws = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))

The collection is one-based: its first worksheet is item 1, and its last worksheet is item Worksheets.Count. If the workbook contains no worksheets, this placement expression cannot work because there is no existing worksheet to refer to. In an ordinary Excel workbook, however, at least one worksheet is present.

Use Before when the new tab should appear ahead of a known sheet, and After when it should follow one. If you omit both arguments, Excel chooses a default location based on the active sheet. Explicit placement is more predictable in automated workflows, especially when users might have a different tab selected.

A complete macro for a named sheet

This version targets the workbook that contains the VBA code, puts the new worksheet after its last worksheet, and checks for an existing name before adding anything. The helper function compares names without regard to capitalization, matching Excel’s treatment of worksheet names.

Sub AddSummarySheetSafely()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim targetName As String

    ' Target the workbook that contains this macro.
    Set wb = ThisWorkbook
    targetName = "Summary"

    ' Leave the workbook unchanged if the name is already taken.
    If WorksheetExists(wb, targetName) Then
        MsgBox "A worksheet named " & targetName & " already exists."
        Exit Sub
    End If

    ' Add after the last worksheet, then set its tab name.
    Set ws = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
    ws.Name = targetName
End Sub

Private Function WorksheetExists(ByVal wb As Workbook, _
                                 ByVal targetName As String) As Boolean
    Dim ws As Worksheet

    ' Compare each worksheet name without case sensitivity.
    For Each ws In wb.Worksheets
        If StrComp(ws.Name, targetName, vbTextCompare) = 0 Then
            WorksheetExists = True
            Exit Function
        End If
    Next ws
End Function

The function returns True as soon as it finds a matching worksheet; otherwise, its default return value remains False. The macro then either stops with a message or creates and names the new sheet. This approach avoids using error suppression to hide an issue that the code can check directly.

Worksheet naming rules to know

Excel limits a worksheet name to 31 characters and does not allow a name to be blank. Names also cannot contain the characters :, \, /, ?, *, [, or ]. A name cannot begin or end with an apostrophe. These restrictions apply whether you type the name in the interface or assign it in VBA.

Worksheet names must be unique within a workbook, and capitalization does not make two otherwise identical names distinct. For instance, Summary and summary conflict. The same name can be used in a different workbook because uniqueness is checked within each workbook, not across every file open in Excel.

If a name comes from a cell or a user prompt, validate it before assigning it. A value that looks sensible to a person may contain a prohibited character, be longer than 31 characters, or already be in use. You can either show a useful message, ask for a different name, or create a distinct name by adding a suffix. If you add a suffix, remember that the complete name must still fit the 31-character limit.

Prevent duplicate names and handle errors

A common misconception is that assigning a name to a new worksheet will rename an existing sheet with that name. It will not: the assignment fails because the workbook already has a worksheet with that name. Decide what your macro should do in that situation instead of allowing an unexpected runtime error to interrupt a larger task.

Stop and notify: Check whether the name exists, then display a message or exit. This is appropriate when the requested name has a specific meaning and should not be changed.
Choose another name: Add a number or another clear suffix, such as Summary 2, and check that the resulting name is available.
Reuse the existing sheet: If the process is meant to update a report, find the existing worksheet and write to it rather than creating another one. This should be an intentional design choice.
Replace the existing sheet: Deleting a sheet can permanently remove its contents and may break formulas or references. Only do this when replacement is explicitly required and the workbook has been backed up.

Other errors can come from targeting the wrong workbook, referring to a worksheet that does not exist, or trying to use an invalid name. Qualifying the workbook and worksheet in the code helps prevent ambiguity. For example, ThisWorkbook.Worksheets("Instructions") states exactly which workbook to search, while Worksheets("Instructions") without a workbook qualifier can refer to the active workbook.

ThisWorkbook, ActiveWorkbook, and the target file

ThisWorkbook means the workbook that contains the running VBA code. It is often the right choice when a macro is stored in the workbook it is meant to update. For example, a macro inside a report file can use ThisWorkbook to add a tab to that report even if another workbook is active.

ActiveWorkbook means the workbook currently active in Excel. It may be appropriate for a macro stored in a personal macro workbook or an add-in that acts on whichever file the user has selected. But it can target the wrong file if focus changes or the user activates another workbook. Choose deliberately and qualify subsequent references with the selected workbook variable.

This distinction matters when code is run from the Visual Basic Editor, a button, or a personal macro workbook. If the intended target is a specific open file, you can also set a workbook variable to that file after checking that it is open. Avoid relying on whichever tab happens to be selected when the macro starts.

Run the macro and save the workbook correctly

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. Choose Insert > Module, paste the macro into the module, and run it with the cursor inside the procedure by pressing F5. You can also return to Excel, open Developer > Macros, select the macro, and choose Run. If Developer is not visible, enable it under File > Options > Customize Ribbon.

On a Mac, the Visual Basic Editor shortcut is commonly Option+F11; depending on the keyboard, you may need Fn as well. You can use the Developer tab’s Visual Basic and Macros commands if available, and enable the tab through Excel’s ribbon and toolbar settings. Excel for the web can open and edit many workbook contents, but it does not run VBA macros; use the desktop Excel application to execute this code.

Save a workbook containing VBA in a macro-enabled format such as .xlsm or in the Excel binary workbook format .xlsb. Saving as a standard .xlsx file does not preserve VBA code. Excel may also block macros according to its security settings or the file’s source. Only enable macros in files you trust; do not bypass security warnings for an unknown workbook.

When to use VBA instead of a manual method

For a one-time task, the interface is often quicker: select the plus button beside the sheet tabs, then rename the new tab by double-clicking it or using the sheet-tab context menu. VBA is more useful when the same operation must happen repeatedly, when a workbook needs a predictable sheet order, or when a macro creates a new report as part of a larger process.

VBA can also fill the new sheet immediately after creating it. For example, after assigning the new worksheet to ws, later statements can write a title with ws.Range("A1").Value = "Monthly Summary" or format cells on that specific sheet. Using the variable is safer than relying on ActiveSheet, because the active tab can change during a macro.

If a task should run in Excel for the web, VBA is not a suitable automation method because the web application does not execute VBA. Office Scripts may be an alternative for supported Microsoft 365 environments, but they use a different language and execution model; VBA code cannot simply be pasted into an Office Script.

Frequently Asked Questions

How do I add a worksheet with a specific name in VBA?
Assign the new worksheet to a variable, then set its Name property: Set ws = ThisWorkbook.Worksheets.Add followed by ws.Name = "Summary". Check first that the workbook does not already contain that name.

How do I add the new worksheet at the end?
Pass the last worksheet as the After argument: Set ws = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)). This places it after the last worksheet in that workbook.

Why does VBA give an error when I name the new sheet?
The name may already exist, exceed 31 characters, be blank, or contain a prohibited character. Check uniqueness and validate the name before assigning it. Also confirm that the code is targeting the workbook you expect.

Can I use a cell value as the worksheet name?
Yes. Read the cell’s value into a string variable and assign that variable to the worksheet’s Name property. Validate the text first for length, prohibited characters, and duplicates; cell contents are not automatically made valid sheet names.

Can I run this VBA in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to execute the macro, or consider a different automation tool if the process must run in the browser.

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.