Excel VBA: Move a Worksheet to the End

VBA & macros · Updated November 21, 2026

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.

  1. Open the workbook and press Alt+F11 to open the Visual Basic Editor.
  2. In the editor, choose Insert > Module, then paste the macro into the new module.
  3. Change the sheet name in the code to match the worksheet tab exactly.
  4. 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

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.