To add a worksheet after the workbook’s last tab and name it in Excel VBA, use Worksheets.Add with the last item in the workbook’s Sheets collection as the After position, then set the new worksheet’s Name. The macro below creates a sheet called Summary and stops first if that name is already in use.
Save a copy of the workbook before running any macro: VBA changes cannot be undone with Ctrl+Z.
Sub AddSummaryAtEnd()
Dim wb As Workbook
Dim addedSheet As Worksheet
Dim existingSheet As Object
Set wb = ThisWorkbook
' Do not create a duplicate sheet name.
Set existingSheet = Nothing
On Error Resume Next
Set existingSheet = wb.Sheets("Summary")
On Error GoTo 0
If Not existingSheet Is Nothing Then
MsgBox "A sheet named Summary already exists."
Exit Sub
End If
' Add the worksheet after the workbook's last sheet tab.
Set addedSheet = wb.Worksheets.Add(After:=wb.Sheets(wb.Sheets.Count))
addedSheet.Name = "Summary"
End Sub
What “at the end” means in VBA
Excel displays worksheets and chart sheets as tabs in a workbook. VBA has a Worksheets collection for worksheets and a broader Sheets collection for all sheet types, including chart sheets. The code uses the last item in wb.Sheets as the insertion point. This places the new worksheet after the last visible sheet tab, even if that last tab is a chart sheet.
Using After:= is important because Worksheets.Add otherwise adds the new worksheet before the active sheet by default. That default may put it somewhere unexpected. In the example, wb.Sheets.Count returns the number of sheet tabs, and the item at that index is the last one. The new sheet is inserted immediately after it.
The code then stores the new worksheet in addedSheet and assigns its name. Keeping a reference to the worksheet makes it clear which sheet is being renamed; it avoids relying on whichever sheet happens to be active later in a longer macro.
Choose the workbook the macro should change
The example sets wb to ThisWorkbook, meaning the workbook that contains the VBA code. This is usually the right choice when the macro is saved inside the workbook it should edit. It is more predictable than relying on whichever workbook Excel currently considers active.
If the macro is stored in a separate macro workbook, such as PERSONAL.XLSB, ThisWorkbook refers to that container, not necessarily the workbook you are looking at. If you intentionally want to change the currently active workbook, replace the assignment with Set wb = ActiveWorkbook. Be careful: the active workbook can change if a macro opens or switches between workbooks. Check the target workbook before running the procedure.
In a larger macro, you can also pass a workbook into a procedure rather than choose one implicitly. That approach is useful when a routine must operate on a specific workbook selected or opened earlier in the code, but it is not necessary for a simple one-workbook task.
Set up and run the macro
- In Excel for Microsoft 365 on Windows, open the workbook and press Alt+F11 to open the Visual Basic Editor. On a Mac, use Option+F11; depending on the keyboard, you may also need the Fn key.
- In the editor, choose Insert > Module. Paste the procedure into the module, then return to Excel.
- Save the workbook as an Excel Macro-Enabled Workbook (
.xlsm) if it is not already saved in a macro-capable format. A regular.xlsxfile cannot store VBA code. - Run the procedure from the Visual Basic Editor, or in Excel choose Developer > Macros, select
AddSummaryAtEnd, and choose Run. If the Developer tab is hidden, enable it in Excel’s ribbon settings.
Excel for the web can open a macro-enabled workbook, but it cannot run or edit VBA macros. Open the file in the desktop application to use this procedure. On Mac, the Visual Basic Editor and VBA are available in desktop Excel, although keyboard shortcuts and some workbook features can differ from Windows.
Use a different sheet name
To choose another name, replace "Summary" in both places in the example: the duplicate-name check and the assignment to addedSheet.Name. For example, use "April Report" for a sheet with that tab name. The duplicate check and assigned name must match; otherwise the macro checks for one name but creates another.
Excel applies naming rules to worksheet tabs. A name must be unique within the workbook, cannot be blank, and can contain no more than 31 characters. It cannot include a colon, backslash, slash, question mark, asterisk, or square bracket, and it cannot begin or end with an apostrophe. Names that differ only in letter case are not distinct for this purpose. For example, Summary and SUMMARY conflict.
These restrictions can matter when the name comes from a cell or a variable. If your code builds a name from a date, customer, or report title, validate its length and remove forbidden characters before assigning it. Otherwise, the macro may create the sheet successfully but fail when it tries to set the invalid name. Also consider whether truncating a long name could produce a duplicate of an existing tab.
Why check for a duplicate first?
Sheet names must be unique across the workbook’s sheets, including chart sheets. If a sheet named Summary already exists, assigning that name to a newly added worksheet raises a run-time error. Without a check, the macro may have already inserted a blank worksheet by the time it stops, leaving an unexpected extra tab behind.
The example looks up wb.Sheets("Summary"). VBA raises an error when that item does not exist, so On Error Resume Next is used briefly for the lookup, then turned off immediately with On Error GoTo 0. If the lookup found an item, existingSheet is not Nothing and the macro exits before adding anything. This local use is different from leaving error suppression on for the whole macro, which can hide unrelated problems.
The current response to a duplicate is to show a message and stop. Another valid design is to choose a numbered name, such as Summary 2, but that requires a loop that checks each candidate before adding the sheet. Stopping is often safer for reports because silently choosing a new name may cause later code or users to refer to the wrong tab.
Common errors and how to fix them
- “A sheet with that name already exists.” Check for an existing worksheet or chart sheet with the same name, or use a different valid name. The example avoids this error by checking before it adds a worksheet.
- “The name is not valid.” Check the 31-character limit, prohibited characters, blank names, and leading or trailing apostrophes. If the name comes from a cell, inspect the value after any cleanup or truncation.
- The macro adds the sheet to an unexpected workbook. Confirm whether
ThisWorkbookorActiveWorkbookis appropriate. The former is the code’s container; the latter is the workbook currently active in Excel. - Excel will not add a sheet. The workbook’s structure may be protected. A protected workbook structure can prevent adding, deleting, moving, or renaming sheets. Check Review > Protect Workbook and follow the workbook owner’s process for removing protection; do not attempt to bypass protection.
- The macro does not appear to run. Confirm the file is saved as
.xlsm, that you opened it in desktop Excel, and that the macro is permitted by your organization’s security settings. Do not enable macros in a file from an untrusted source simply to run this code.
Alternative placement and naming patterns
If “at the end” means after the last worksheet only, rather than after the last tab of any type, you can use the last item in wb.Worksheets as the insertion point. That distinction matters only when the workbook contains chart sheets. The main example uses wb.Sheets because readers commonly mean the end of the complete tab bar.
To add the sheet before a particular worksheet instead, use that worksheet as the Before argument. For example, a report sheet could be inserted immediately before a worksheet named Archive. To add it at the beginning, use the first sheet as the Before target. These choices control position; the separate Name assignment controls the tab label.
For repeatable automation, keep the target workbook and intended sheet name in variables, check for conflicts, and handle any expected protection or invalid-name conditions explicitly. Avoid depending on ActiveSheet when the macro needs a precise insertion position: the active tab is a user-interface state, not a dependable definition of “last.”
Frequently Asked Questions
What is the simplest VBA command to add a sheet at the end?
Use Worksheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)). Store the returned worksheet in a variable if you need to name it or work with it afterward.
How do I add and name a sheet in one macro?
Assign the result of Worksheets.Add to a worksheet variable, then set that variable’s Name property, as in addedSheet.Name = "Summary". Check for a name conflict first to prevent a run-time error.
Does the new worksheet become active?
Excel normally activates a worksheet when it is added. The example does not rely on that behavior: it refers to the new sheet through addedSheet, which is more reliable if the macro later selects or activates other sheets.
Can I run this VBA in Excel for the web?
No. Excel for the web does not run VBA macros. Open the macro-enabled workbook in desktop Excel for Windows or Mac to run the procedure.
Why use Sheets.Count instead of Worksheets.Count?Sheets.Count includes worksheet and chart-sheet tabs, so it identifies the end of the whole tab bar. Worksheets.Count counts only worksheets and may not identify the last tab if a chart sheet follows them.
Related
- Excel Shortcut to Refresh a PivotTable and All Data
- Excel Shortcut to Select All Data in a Column Quickly
- Excel Shortcut to Select a Whole Column in Seconds
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel Sum by Color: Formulas, Filters, and VBAExcel has no built-in SUM function that adds cells based on their fill color. To total cells of…
- Excel VBA Error: Constant Expression Required ExplainedIn Excel VBA, “Constant expression required” is a compile error: VBA found a value that must be known…
- VBA Turn Off AutoFilter in Excel Without Losing DataTo turn off a worksheet AutoFilter in VBA, first show all rows if a filter is active, then…
- Filter Dictionary Keys in Excel VBA: Code and OptionsYes. In Excel VBA, filter a Scripting.Dictionary by looping through its keys and copying the matching key/value pairs…
- Google Sheets Macros: Examples and How to Use ThemGoogle Sheets macros are saved actions you can replay to automate repetitive work, such as formatting a report,…
- Excel VBA Freeze Worksheet: Freeze Panes or Lock CellsIn Excel VBA, use ActiveWindow.FreezePanes to keep rows or columns visible while you scroll; use worksheet protection when…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.