VBA Code to Add a New Sheet in Excel: Examples and Tips

VBA & macros · Updated November 20, 2026

To add a new worksheet at the end of the workbook and give it a unique name, use a VBA macro like the one below. In Excel for Microsoft 365 on Windows, put it in a standard module, then run it from the Macros dialog; it creates names such as Report 1 and Report 2 instead of failing when a name already exists.

VBA code to add and name a worksheet

This example adds a worksheet after the last sheet tab, checks existing sheet names without regard to capitalization, and chooses the first available Report number. The check includes chart sheets as well as worksheets, because sheet names must be unique across the workbook.

Sub AddReportSheet()
    Dim wb As Workbook
    Dim newSheet As Worksheet
    Dim existingSheet As Object
    Dim baseName As String
    Dim candidateName As String
    Dim suffix As Long
    Dim nameIsUsed As Boolean

    ' Use the workbook that contains this macro.
    Set wb = ThisWorkbook
    baseName = "Report"
    suffix = 1

    ' Find an unused name, including names of chart sheets.
    Do
        candidateName = baseName & " " & suffix
        nameIsUsed = False

        For Each existingSheet In wb.Sheets
            If StrComp(existingSheet.Name, candidateName, vbTextCompare) = 0 Then
                nameIsUsed = True
                Exit For
            End If
        Next existingSheet

        If Not nameIsUsed Then Exit Do
        suffix = suffix + 1
    Loop

    ' Add the worksheet after the last tab and assign its name.
    Set newSheet = wb.Worksheets.Add(After:=wb.Sheets(wb.Sheets.Count))
    newSheet.Name = candidateName
End Sub

For example, if the workbook already has sheets named Report 1 and Report 2, the macro creates Report 3. It operates on the workbook containing the code, not necessarily whichever workbook happens to be active. That distinction helps avoid adding a sheet to the wrong file when several workbooks are open.

How the macro works

The Worksheets.Add method creates a worksheet. Its optional placement arguments let you specify where the new tab goes: Before or After an existing sheet. The macro supplies the last sheet in the workbook as the After reference, so the new worksheet appears at the end. The method returns a reference to the sheet it created, which the macro stores in a worksheet variable before setting the name.

The loop builds a candidate name, looks through every sheet in the workbook, and checks whether that name is already in use. StrComp with vbTextCompare makes the comparison case-insensitive, matching Excel’s treatment of sheet-name collisions. If the name exists, the suffix increases and the loop tries again. This avoids the common runtime error caused by assigning a name that another tab already uses.

ThisWorkbook means the workbook where the macro is stored. ActiveWorkbook means the workbook currently in front. Use the latter only when you intentionally want the macro to act on the active file; it can be risky if the user switches workbooks before running the macro.

Choose where the new sheet goes

Position is controlled by the reference passed to the Add method. The example places a worksheet after the final tab, which is useful for logs, monthly reports, or generated output. To insert it before the first tab instead, use the first worksheet as the placement reference. To put it next to a particular worksheet, use that worksheet as the reference.

  • At the end: specify the last sheet as the After reference, as in the example.
  • At the beginning: specify the first worksheet as the Before reference.
  • Beside a known tab: specify that tab with either Before or After, depending on the desired order.

If you omit both placement arguments, Excel inserts the new sheet before the active sheet. That default can surprise people: it does not necessarily put the new tab at the end. Explicit placement makes the result predictable. If the workbook includes chart sheets, remember that the tab order can include those too; the example’s final-sheet reference uses the workbook’s Sheets collection for that reason.

Names, duplicates, and Excel’s limits

Excel worksheet names can contain up to 31 characters. They cannot be blank or contain a colon, backslash, slash, question mark, asterisk, or square brackets. Names also need to be unique within the workbook, without relying on capitalization to distinguish them. The example uses a short fixed prefix and a numeric suffix, so it stays within the length limit as it increments under ordinary use.

If you prefer a fixed name such as Summary, decide what should happen when that name already exists. You could stop and show a message, reuse the existing sheet, or create a numbered alternative. Reusing a sheet is not the same as adding a new one: it may preserve old data or formatting, so make that behavior deliberate. For names built from user input or long dates, validate the text and keep the 31-character limit in mind before assigning it.

Also distinguish the worksheet tab name from the worksheet’s VBA code name. Renaming a tab changes the name users see in Excel; the code name is a separate VBA identifier. For a routine that only creates and names a tab, changing the tab name is normally all that is needed.

Put the macro in a module and run it

  1. Save a copy of the workbook before running any macro. VBA changes cannot be reversed with Ctrl+Z.
  2. Open the VBA editor with Alt+F11 on Windows. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon and select Developer.
  3. In the editor, choose Insert > Module, then paste the complete macro into the module window.
  4. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if it needs to retain the macro.
  5. Return to Excel and choose Developer > Macros, select AddReportSheet, and click Run. On Windows, Alt+F8 also opens the Macro dialog.

When the macro finishes, Excel normally activates the newly added worksheet. If you run the same macro again, it will create the next available numbered Report tab. To edit the code later, reopen the VBA editor and change the module; a worksheet formula or normal cell entry cannot run this VBA routine by itself.

Windows, Mac, and Excel for the web

The example uses standard Excel VBA objects and is suitable for desktop Excel for Microsoft 365 on Windows. Excel for Mac also supports VBA, and the basic worksheet-creation approach is similar. Depending on the keyboard and macOS function-key settings, the VBA editor shortcut may require Option+F11 or Fn+Option+F11; the Developer tab’s Visual Basic command is another route. Ribbon and dialog details can differ slightly on Mac.

Excel for the web does not run VBA macros or provide the desktop VBA editor. A workbook containing VBA can be opened there, but run this macro in the desktop Excel application. If you need automation that works in a browser, consider Office Scripts instead; they are a separate automation tool with a different language and setup, not VBA code that can simply be pasted into a module.

Common problems and safe use

  • The macro is missing from the list: confirm that the procedure is in a standard module, is declared as a public Sub without required arguments, and is saved in the workbook you expect.
  • Excel blocks the macro: check the file’s source and trustworthiness before changing security settings. Do not enable macros in an unfamiliar workbook just to run a sheet-creation routine.
  • The workbook was saved as .xlsx: that format does not preserve VBA code. Save a macro-bearing file as .xlsm, then confirm the code remains in the saved copy.
  • A name assignment fails: check for an existing tab with the same name and for prohibited characters or a name longer than 31 characters. The supplied macro handles duplicate Report names but does not sanitize arbitrary user-provided text.
  • The sheet appears in an unexpected workbook: the example uses the workbook containing the macro. Confirm where the code is stored, especially when multiple files are open.

Keep macros limited to tasks you understand, and retain an untouched backup when a workbook contains important data. Since Ctrl+Z cannot undo a macro’s actions, test a new routine on a copy first. Macro security settings vary by organization, and a workplace may restrict running or storing VBA; follow its policies rather than lowering protections globally.

When to use a macro or another method

For one sheet, adding a tab with Excel’s plus button is faster than writing VBA. A macro is useful when the same operation must happen repeatedly, when a process needs a predictable name and position, or when other VBA code needs to create sheets as part of a larger workflow. For browser-based automation, Office Scripts may fit better, but it is not interchangeable with VBA. Choose based on where the workbook runs and whether the task needs to repeat automatically.

Frequently Asked Questions

What is the simplest VBA code to add a sheet?
Use a short procedure that declares a worksheet variable and assigns it to the result of Worksheets.Add. Excel inserts a sheet, but without a placement argument it goes before the active sheet. Add an explicit Before or After reference when the position matters.

How do I add a sheet at the end of a workbook?
Pass the last sheet as the After argument to the Add method. The example does this and then names the new worksheet. It adds the sheet after the last tab rather than relying on Excel’s default insertion position.

How do I name a new sheet without a duplicate-name error?
Check the workbook’s existing sheet names before assigning the new name, then choose an unused alternative if a match exists. The example searches for Report 1, Report 2, and so on, comparing names without regard to capitalization.

Can I run VBA to add a sheet 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 run the macro; for web automation, investigate Office Scripts instead.

Can I undo a macro with Ctrl+Z?
No. Excel clears its normal undo history when a VBA macro runs, so Ctrl+Z does not reverse the macro’s changes. Save a separate copy before running code that changes 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.