Excel VBA: Add a New Sheet and Rename It Automatically

VBA & macros · Updated November 19, 2026

To add a worksheet and give it a name with Excel VBA, use Worksheets.Add to create the sheet, then assign its Name property. For example, newSheet.Name = "Summary" renames the worksheet to Summary; the complete macro below also checks that the name is not already in use.

A complete macro to add and rename a worksheet

In this example, the new worksheet is placed at the end of the workbook and named Summary. The helper function checks all sheets in the workbook, including chart sheets, before the macro adds anything. That avoids the common error caused by trying to assign a name that already exists.

Sub AddAndRenameSheet()
    Dim wb As Workbook
    Dim newSheet As Worksheet
    Dim sheetName As String

    ' Use the workbook that contains this VBA code.
    Set wb = ThisWorkbook
    sheetName = "Summary"

    ' Stop before adding a sheet if the name is already taken.
    If SheetNameExists(sheetName, wb) Then
        MsgBox "A sheet named " & sheetName & " already exists.", vbInformation
        Exit Sub
    End If

    ' Add a worksheet after the last sheet, then assign its name.
    Set newSheet = wb.Worksheets.Add(After:=wb.Sheets(wb.Sheets.Count))
    newSheet.Name = sheetName
End Sub

Function SheetNameExists(ByVal sheetName As String, ByVal wb As Workbook) As Boolean
    Dim item As Object

    ' Compare without regard to upper- or lowercase letters.
    For Each item In wb.Sheets
        If StrComp(item.Name, sheetName, vbTextCompare) = 0 Then
            SheetNameExists = True
            Exit Function
        End If
    Next item
End Function

Put both procedures in a standard VBA module in the same workbook. The first procedure is the macro you run; the function beneath it supplies the name check. VBA treats an unset Boolean return value as False, so the function returns False when it reaches the end without finding a match.

How the add-and-rename steps work

Worksheets.Add creates a worksheet. Its optional Before or After argument determines where it appears in the tab order. In the example, After:=wb.Sheets(wb.Sheets.Count) places it after the last sheet of any type. If you omit those arguments, Excel inserts the new worksheet before the active sheet.

The line newSheet.Name = sheetName assigns the tab name. It is better to keep the worksheet in a variable, as the example does, than to refer to a bare ActiveSheet. Adding or activating sheets can change which sheet is active, and other code or user actions can change it again. A variable makes clear exactly which new worksheet the macro is renaming.

The example uses ThisWorkbook, meaning the workbook that contains the running VBA code. This is usually appropriate when the macro is saved inside the workbook it should edit. If the macro is stored in the Personal Macro Workbook or an add-in, ThisWorkbook refers to that file instead. In that case, deliberately set wb to the intended workbook, such as a workbook variable you opened or selected, rather than assuming the macro’s own file is the target.

Run the macro in Excel for Windows

  1. Save a copy of the workbook before running a macro. VBA changes cannot be reversed with Ctrl+Z.
  2. Save the working file as an Excel Macro-Enabled Workbook (.xlsm) if it needs to retain its VBA code.
  3. Press Alt+F11 to open the Visual Basic Editor. In the editor, choose Insert > Module, then paste both procedures into the module.
  4. Return to Excel. Press Alt+F8, select AddAndRenameSheet, and choose Run. If the Summary tab already exists, the macro displays a message and exits without adding another worksheet.

You can also run the procedure from the Visual Basic Editor by placing the cursor inside AddAndRenameSheet and pressing F5. If the Developer tab is visible, Developer > Macros opens the macro list. To display that tab in Windows Excel, use File > Options > Customize Ribbon and select Developer.

Set the name and insertion position

To use a different fixed name, change sheetName = "Summary" to another valid name, such as sheetName = "November Report". The same value is used for both the duplicate check and the final rename, so there is no risk of checking one name and assigning another by mistake.

To insert the worksheet before a particular existing tab, pass that worksheet to the Before argument. For example, after setting a worksheet variable called reportSheet, the add line can be written as Set newSheet = wb.Worksheets.Add(Before:=reportSheet). The referenced sheet must belong to the workbook being changed. To put the new tab first, use the first sheet in the workbook as the Before target; to put it last, retain the example’s After argument.

Another common request is to name the sheet using a cell value, such as a department or reporting period. Read the cell’s value into sheetName before the existence check, and check that it is not blank and meets Excel’s naming rules. If that value comes from user input, validation matters: an invalid name can cause the assignment to fail after the sheet has been created.

Worksheet naming rules to know

Excel sheet names are limited to 31 characters and cannot contain a colon, backslash, slash, question mark, asterisk, or square brackets. A name cannot be blank, and sheet names must be unique within the workbook. Comparisons are effectively case-insensitive for uniqueness, so Summary and SUMMARY are not two separate available names. These restrictions apply whether a name is typed by a person or assigned in VBA.

If a proposed name may be too long or contain prohibited characters, validate or clean it before assigning it. Do not silently cut off a name unless that is suitable for the data: two different long names may become identical after truncation. Similarly, replacing forbidden characters with hyphens can create collisions, so run the duplicate check on the final name that will actually be assigned.

A worksheet name is a tab label, not a cell value or a workbook filename. Renaming a tab does not rename the workbook, and it does not automatically change ordinary text that happens to mention the old tab name. Excel generally updates formula references when a referenced worksheet is renamed, but VBA code that refers to a sheet by its literal name may need to be updated too. Code using a worksheet’s VBA CodeName is a separate approach and is not the same as the visible tab name.

Avoid duplicate names and handle errors

Without a name check, a macro can add the worksheet successfully and then fail on the rename if another sheet already has that name. That leaves the workbook with an extra default-named tab. Checking first, as the complete example does, prevents that particular partial result. The check includes chart sheets because Excel requires sheet names to be unique across the workbook’s sheets, not just among worksheets.

If a duplicate should result in a different sheet name rather than stopping, change the macro’s policy: for example, build a candidate name such as Summary 2, check whether it exists, and increment the number until an available name is found. Make sure the resulting candidate still fits the 31-character limit. Choosing whether to stop, reuse an existing tab, or create a numbered tab depends on the workflow; those choices have different consequences and should not be left to an accidental VBA error.

Other failures can occur if workbook structure is protected, if the workbook is read-only, or if the proposed name violates Excel’s rules. A protected workbook structure can prevent adding, deleting, moving, or renaming sheets. Check the workbook’s protection settings and the input name instead of suppressing every error with broad error-handling code, which can hide the actual cause.

Windows, Mac, and Excel for the web

Excel for Microsoft 365 on Windows supports VBA macros in desktop workbooks. Excel for Microsoft 365 for Mac also supports VBA, though its menus and keyboard shortcuts can differ. On Mac, use Tools > Macro > Visual Basic Editor if a Windows shortcut is unavailable or behaves differently; some keyboards require the Fn key with function-key shortcuts. The VBA statements in this example are the same on desktop Windows and Mac.

Excel for the web does not run VBA macros. You can open and edit many macro-enabled workbooks in a browser, but to execute this procedure, open the workbook in the desktop Excel application. If a workbook must be automated in the browser, Office Scripts may be an alternative, but Office Scripts use TypeScript and are not VBA; the code cannot simply be pasted into an Office Script.

Macro safety and file handling

Only run VBA code you understand and trust. A macro can make changes that are not easy to reverse, and Ctrl+Z does not undo VBA actions. Keep a backup copy before trying a new procedure, especially if it will be adapted to add, rename, or delete many sheets. Save macro-containing files as .xlsm; saving as a macro-free workbook format can remove the VBA project.

When Excel displays a security warning, do not enable macros indiscriminately. Follow your organization’s security rules and enable code only when you trust its source and purpose. A short sheet-creation macro still changes the workbook, so the backup and review steps are worthwhile.

Frequently Asked Questions

How do I add a worksheet and name it in one VBA line? You can use Worksheets.Add.Name = "Summary" in a simple situation, but a separate worksheet variable is clearer and makes it easier to choose the insertion position and check for duplicate names.

How do I add a sheet at the end of a workbook? Use the After argument with the last sheet, as in the full example: Worksheets.Add(After:=Sheets(Sheets.Count)). Qualify the references with the intended workbook in reusable code so the macro does not add a tab to the wrong file.

Why does VBA say the name is already taken? A worksheet or chart sheet in the workbook may already use that name. Excel does not treat uppercase and lowercase versions as distinct names. Check the workbook’s tabs and confirm that the macro checks the final name it assigns.

Can I use a cell’s value as the new sheet name? Yes. Read the cell value into a string variable, then validate its length and characters and check for an existing sheet with that name before adding or renaming. Handle blank cells and duplicate values explicitly.

Can I run this VBA macro in Excel for the web? No. Open the macro-enabled workbook in desktop Excel for Windows or Mac to run VBA. For browser-based automation, investigate Office Scripts separately; they use a different language and model.

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.