To move a worksheet to the last tab in its workbook with VBA, use Worksheet.Move and set its After argument to the workbook’s last sheet. For example, Worksheets("Summary").Move After:=Sheets(Sheets.Count) moves the Summary worksheet to the end; the complete, safer macro below also makes clear which workbook it changes and avoids an unnecessary move if the sheet is already last.
The basic VBA method
Excel’s Move method changes a sheet’s position in the workbook. The After argument names the sheet that should immediately precede the moved worksheet. Since Sheets.Count returns the number of sheets in the workbook, Sheets(Sheets.Count) refers to the current last tab. Putting those pieces together moves the named worksheet after that tab.
This operation moves the existing worksheet; it does not create a copy. Its cells, formulas, formatting, and worksheet-level features stay with it. Formulas elsewhere in the workbook that refer to the moved sheet generally continue to refer to that sheet, because its name has not changed. Its position among the tabs changes, which can matter to people who use tab order as a visual or procedural guide.
A one-line command is useful in a quick procedure, but it relies on Excel’s active workbook context if the workbook is not specified. For a repeatable macro, explicitly identify the target workbook and worksheet. That reduces the chance of moving a same-named sheet in the wrong open file.
Use a complete macro with explicit references
This example moves the worksheet named Summary to the final tab of the workbook containing the VBA code. It checks whether Summary is already last, so it does not try to move a sheet after itself.
Sub MoveSummaryToEnd()
Dim wb As Workbook
Dim ws As Worksheet
' Use the workbook that contains this code.
Set wb = ThisWorkbook
Set ws = wb.Worksheets("Summary")
' Skip the move if this worksheet is already the final tab.
If ws.Name <> wb.Sheets(wb.Sheets.Count).Name Then
ws.Move After:=wb.Sheets(wb.Sheets.Count)
End If
End Sub
Change Summary to the exact worksheet tab name, including spaces and punctuation. For example, if the tab is named Quarterly Results, put that name inside the quotation marks. The code uses Worksheets to find the source because the source is a worksheet, and Sheets to find the final tab because that collection includes every sheet type.
ThisWorkbook means the workbook that stores the macro. That is often the right choice when the code is saved inside the workbook it should edit. It is not necessarily the workbook currently selected in Excel: if the code lives in an add-in or Personal Macro Workbook, ThisWorkbook points to that file instead. In that situation, set wb to the intended open workbook explicitly, or use ActiveWorkbook only when you are certain the correct workbook is active.
Why Sheets and Worksheets are different
In Excel VBA, Worksheets contains worksheet tabs, while Sheets contains all sheet tabs, including chart sheets. A chart sheet displays a chart as its own sheet rather than as a chart embedded in a worksheet. If a workbook has no chart sheets, the two collections usually appear to give the same result, which is why examples sometimes use them interchangeably.
For “move this worksheet to the end of the workbook,” using wb.Sheets(wb.Sheets.Count) places the worksheet after the last tab of any type. If the last tab is a chart sheet, the worksheet goes after that chart sheet. If instead you want it immediately after the last worksheet, regardless of chart sheets that might follow, use the last item in the Worksheets collection as the destination:
ws.Move After:=wb.Worksheets(wb.Worksheets.Count)
That alternative defines “end” as the end of the worksheet sequence, not necessarily the far-right tab in the workbook. Choose based on what the reader means by “end.” When the goal is the final visible tab in the tab row, use the final item in Sheets.
How to add and run the macro
In Excel for Microsoft 365 on Windows, save a copy of the workbook first. VBA changes cannot be reversed with Ctrl+Z, so a backup is the straightforward way to recover the original tab order if the macro changes the wrong workbook or sheet. A workbook containing VBA generally needs to be saved as an Excel Macro-Enabled Workbook (.xlsm) to retain its code.
- Open the workbook and press Alt+F11 to open the Visual Basic Editor.
- In the editor, choose Insert > Module, then paste the macro into the new module.
- Change the sheet name in the code to match the worksheet tab exactly.
- Return to Excel and run the procedure from the Macro dialog, opened with Alt+F8, or run it from the Visual Basic Editor.
On a Mac, Excel also supports VBA, though keyboard shortcuts and some editor details can differ by keyboard and Excel version. Use the Developer tab or the Macros command to open and run macros if the Windows shortcuts are not available. Excel for the web does not run VBA macros; open the workbook in the desktop Excel app to use this code. Only enable macros in files you trust, since VBA can perform actions beyond rearranging sheets.
Common errors and practical safeguards
If Excel reports that the subscript is out of range when it tries to set ws, the requested worksheet name was not found in the workbook referenced by wb. Check spelling, spaces, and which workbook the code targets. A sheet’s tab name is not necessarily the same as its VBA code name, and this example deliberately looks it up by tab name through Worksheets("Summary").
A workbook with its structure protected may prevent users or macros from moving, adding, deleting, hiding, or renaming sheets. If the move fails because of workbook protection, the workbook structure must be unprotected by someone authorized to do so. Do not assume that worksheet protection and workbook structure protection are the same setting: protecting individual cells on a worksheet is a different feature from protecting the arrangement of workbook sheets.
Other useful safeguards include:
- Confirm the workbook name before running the macro, particularly when several workbooks are open.
- Check that the target tab is a worksheet. A chart sheet cannot be assigned to a variable declared as
Worksheet. - Run the macro once on a copy if the workbook has a complex tab layout or contains chart sheets.
- Consider whether other procedures depend on sheet position. Code that refers to sheets by name is generally clearer than code that assumes a sheet is always a particular number.
Moving a tab does not rename it, but it can affect code or instructions that use a numeric sheet position. For example, a procedure that addresses the second sheet by index may refer to a different sheet after the order changes. Review position-dependent automation before using a reorder macro in a workbook that other macros rely on.
Moving one sheet versus sorting or copying
The macro moves one chosen worksheet; it does not sort every worksheet alphabetically, move multiple sheets as a group, or duplicate a sheet. If the requirement is to move several tabs, each move can change the workbook order and therefore the destination index. The sequence of operations matters: a procedure should identify its destination at the time of each move, rather than assume that an earlier position remains unchanged.
To copy instead of move, use the worksheet’s Copy method rather than Move. Copying creates another sheet, while moving retains the original and changes its position. A copied sheet may receive a modified name if the original name is already in use. If the user simply wants to rearrange a tab once, dragging the tab to the right end of the tab row is often easier and does not require enabling macros.
There is no need to calculate a numeric destination for a normal move-to-end task. The expression wb.Sheets(wb.Sheets.Count) finds the current last sheet at runtime, so the same macro can work when other sheets have been added or removed. That is more adaptable than hard-coding a tab number such as 12.
Frequently Asked Questions
How do I move the active worksheet to the end?
Set a Worksheet variable to the active worksheet, then use its Move After:= argument with the last sheet in the intended workbook. Ensure the active tab is a worksheet, not a chart sheet, and explicitly choose the workbook if more than one is open.
Does this move the sheet to the end of all tabs?
Yes, when the destination is wb.Sheets(wb.Sheets.Count). Because Sheets includes chart sheets as well as worksheets, the moved worksheet becomes the final tab after every sheet type.
Can I move a worksheet to the end without VBA?
Yes. In desktop Excel, drag the worksheet tab to the right end of the tab row. If the last tab is not visible, use the sheet-navigation controls to scroll through the tabs, then drag the worksheet to the final position.
Why does the macro say the worksheet name is invalid?
Most often, the name in the code does not exactly match a tab in the workbook being referenced. Verify the tab spelling and workbook target. Also check that the macro is not looking in an add-in or Personal Macro Workbook by mistake.
Can I run this VBA in Excel for the web?
No. Excel for the web can open and edit many macro-enabled workbooks, but it does not execute VBA. Open the file in desktop Excel for Windows or Mac and run the macro there.
Related
- Excel Split Cell at a Specific Character?
- Excel Slicers for Multiple PivotTables: How to Connect
- Excel Named Ranges in Data Validation: How to Use Them
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA MsgBox Result: Return Values and Button ChoicesIn VBA, MsgBox returns a VbMsgBoxResult value that identifies which response button the user selected. Store that return…
- VBA Excel: Check a Cell’s FormatTo check a cell’s format in VBA, read the relevant property of its Range object: use Range("A1").NumberFormat for…
- Excel VBA AutoFilter Off: Clear Filters or Remove ArrowsTo turn off a standard worksheet AutoFilter in Excel VBA, first clear any active filter so the filtered-out…
- VBA Convert String to Uppercase with UCase ExamplesUse VBA’s UCase function to convert a string to uppercase; for a result explicitly typed as a String,…
- Excel VBA: Open a CSV and Copy Its Data to a SheetTo open a CSV file with Excel VBA and copy its data into a worksheet, use Workbooks.OpenText to…
- TMG Macro Recorder? Excel’s Built-In Option Explained“TMG macro recorder” is not the name of a standard feature in Microsoft Excel. If you mean recording…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.