VBA Add Worksheet After the Last Sheet

To add a worksheet after the last sheet tab in Excel, use Worksheets.Add with the last item in the workbook’s Sheets collection as the After argument. The macro below adds a worksheet to the workbook containing the VBA code and names it Summary.

Sub AddWorksheetAfterLastSheet()
    Dim wb As Workbook
    Dim newSheet As Worksheet

    ' Use the workbook that contains this macro.
    Set wb = ThisWorkbook
    Set newSheet = wb.Worksheets.Add(After:=wb.Sheets(wb.Sheets.Count))
    newSheet.Name = "Summary"
End Sub

Save a copy of the workbook before running a macro: adding a sheet cannot be reversed with Ctrl+Z. The example creates a worksheet, not a chart sheet, even if the last existing tab is a chart sheet.

How the VBA statement works

The Worksheets.Add method inserts a new worksheet. Its After argument specifies the sheet that should immediately precede the new one. In the example, wb.Sheets.Count returns the number of sheets, and wb.Sheets(wb.Sheets.Count) refers to the last item in the workbook’s Sheets collection. Together, these expressions place the new worksheet at the end of the tab order.

Excel has both a Sheets collection and a Worksheets collection. Sheets includes every sheet type, including chart sheets; Worksheets includes only worksheet tabs. That distinction matters when a workbook contains a chart sheet at the end. Using the last item in Sheets means “after the last sheet tab of any type,” which is usually what someone means by the last sheet.

The method returns a reference to the worksheet it creates. Assigning that result to newSheet makes it straightforward to rename the new tab, format it, or write data to it in later lines. If a particular name is not needed, remove the newSheet.Name line and let Excel assign its default name.

Choosing the right workbook reference

ThisWorkbook means the workbook where the VBA code is stored. That is usually the safest choice when the macro lives in the workbook it should modify. It avoids accidentally adding a tab to a different workbook that happens to be active when the macro runs.

ActiveWorkbook, by contrast, refers to the workbook currently in focus. It can be appropriate if the macro is stored in a separate personal macro workbook or an add-in and is intended to operate on whichever workbook the user has selected. In that case, replace the assignment with Set wb = ActiveWorkbook and check that the intended workbook is active before running the macro.

This distinction prevents a common surprise: if a macro is stored in PERSONAL.XLSB, ThisWorkbook refers to PERSONAL.XLSB, not to the workbook visible behind it. A workbook-level macro should generally use ThisWorkbook; a reusable macro designed to work on the active file may use ActiveWorkbook deliberately.

Adding after the last worksheet only

If “last sheet” means the last worksheet specifically, regardless of whether a chart sheet follows it, use the Worksheets collection for the position as well:

Sub AddAfterLastWorksheet()
    Dim wb As Workbook
    Dim newSheet As Worksheet

    ' Place the new worksheet after the final worksheet tab.
    Set wb = ThisWorkbook
    Set newSheet = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
End Sub

For example, suppose the tab order is Data, Dashboard (a worksheet), and Sales Chart (a chart sheet). The first macro adds its worksheet after Sales Chart, making it the final tab. The second adds its worksheet after Dashboard and before Sales Chart. Choose the version that matches the intended tab order.

Hidden sheets are still part of the collections and count toward their positions. If the workbook’s final sheet is hidden, inserting after it may not look like inserting after the last visible tab. If that distinction matters, inspect the workbook’s sheet order and visibility before choosing the insertion point.

How to add and run the macro

Save a backup copy. Macros do not support Excel’s normal Ctrl+Z undo history.
In desktop Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. In the editor, choose Insert > Module.
Paste the macro into the module, then return to Excel. Save the file as an Excel Macro-Enabled Workbook (.xlsm) if it needs to retain the VBA code.
Run the macro from the Visual Basic Editor, or return to Excel and use Developer > Macros, select the macro, and choose Run. If the Developer tab is not shown, it can be enabled in Excel Options > Customize Ribbon.

On Mac, Excel also supports VBA in the desktop application. The Visual Basic Editor is available through Tools > Macro > Visual Basic Editor; Option+F11 may also open it, depending on the keyboard and function-key settings. Excel for the web does not run VBA macros, so use desktop Excel to edit or run this code. Macro security settings may require you to allow a trusted file or location before the macro can run.

Renaming the new tab safely

The example assigns the name Summary after creating the worksheet. If the workbook already contains a sheet with that name, Excel raises an error at the rename line. The sheet has already been added by then, so the workbook may be left with a new, default-named worksheet even though the macro did not finish as intended.

Before using a fixed name in a repeated process, decide what should happen if that name is already taken: stop and report the conflict, use a different name, or reuse and clear an existing sheet. Do not silently delete an existing sheet unless that is explicitly intended, because it could contain useful data. Excel sheet names are limited to 31 characters, must be unique within the workbook, and cannot contain characters such as a colon, backslash, slash, question mark, asterisk, or square brackets.

If no specific name is required, leaving out the rename line avoids name-conflict errors. If the process must use a particular name, check for that name before adding the sheet or handle the conflict explicitly. The appropriate behavior depends on whether the macro is creating a fresh report, updating an existing report, or preserving user-entered content.

Common errors and what to check

Excel says the workbook structure is protected. Workbook-structure protection can prevent adding, deleting, moving, or renaming sheets. Check Review > Protect Workbook and remove protection with the authorized password if appropriate. A worksheet’s cell protection is different from workbook-structure protection.
The macro adds a tab to the wrong file. Check whether the code uses ThisWorkbook or ActiveWorkbook. In particular, a macro stored in PERSONAL.XLSB will treat that file as ThisWorkbook.
The sheet appears in an unexpected position. Check whether the code uses Sheets or Worksheets, and whether the workbook contains chart or hidden sheets. Those collections can produce different last items.
The macro stops when it tries to name the tab. Look for an existing sheet with the same name and check the name’s length and characters. The insertion may have succeeded before the rename failed.
The code will not run. Confirm that it is being run in desktop Excel, that macros are permitted by the applicable security settings, and that the workbook is not blocked or opened in a restricted state.

When a macro fails, the Visual Basic Editor often highlights the line that caused the error. Read that line before rerunning the procedure. If the first run created an unwanted blank sheet, inspect the workbook and remove it manually only after confirming it contains nothing that needs to be kept.

Alternatives to VBA

For a one-time change, use the Excel interface: select the final tab, choose Home > Insert > Insert Sheet, and then move the inserted sheet to the desired position if necessary. You can also use the plus button beside the sheet tabs to insert a worksheet, although Excel may place it next to the selected tab rather than at the end. The interface is often simpler when you do not need the action to be repeatable.

VBA is useful when a workbook-generation process needs to create a tab consistently, such as adding a new report sheet after importing data. It can also continue by writing values, applying formatting, or setting a tab color. If the only requirement is to create one blank sheet occasionally, a macro may add unnecessary complexity and require macro-enabled file handling.

Frequently Asked Questions

Does Worksheets.Add add the new sheet at the end by default?

Not reliably as a statement of intent. Without an explicit position, Excel inserts a sheet relative to the active sheet. Specify After:= and the last sheet object to make the desired position clear and repeatable.

Should I use Sheets.Count or Worksheets.Count?

Use Sheets.Count when the new worksheet should follow the final tab of any type, including a chart sheet. Use Worksheets.Count when it should follow the final worksheet, even if a chart sheet comes after it.

Can I add a worksheet after a chart sheet?

Yes. A chart sheet is a member of the workbook’s Sheets collection, and Worksheets.Add can add a worksheet after it when it is supplied as the After argument.

Why does the macro say it cannot add a sheet?

A frequent cause is workbook-structure protection. Also verify that the macro targets the intended workbook and that Excel is allowing the VBA code to run. The error message and highlighted VBA line can help identify which condition applies.

Can I run this macro 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 use the Visual Basic Editor and run the procedure.

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.