To move a worksheet with Excel VBA, use the worksheet’s Move method and specify the sheet it should go before or after. For example, the macro below places the Summary sheet after Data in the same workbook.
Move a worksheet within the same workbook
In Excel for Microsoft 365 on Windows, open the VBA editor with Alt+F11, choose Insert > Module, and put this procedure in the new module. The workbook containing the macro must also contain sheets named Summary and Data.
Sub MoveSummaryAfterData()
Dim wb As Workbook
Dim summarySheet As Worksheet
Dim dataSheet As Worksheet
' Use the workbook that contains this macro.
Set wb = ThisWorkbook
Set summarySheet = wb.Worksheets("Summary")
Set dataSheet = wb.Worksheets("Data")
summarySheet.Move After:=dataSheet
End Sub
Save a copy of the workbook before running a macro: VBA changes cannot be reversed with Ctrl+Z. To run this procedure, place the cursor inside it in the VBA editor and press F5, or return to Excel and run it through Developer > Macros. If the Developer tab is hidden, enable it in Excel Options > Customize Ribbon. On Mac, the Visual Basic Editor is available from Developer > Visual Basic or Tools > Macro > Visual Basic Editor; Option+F11 may also work, sometimes with the Fn key depending on keyboard settings.
The Move operation changes the tab order; it does not create a second sheet. After the procedure runs, Summary sits immediately after Data. If Summary is already there, the call is still valid, though it may not visibly change the order.
How Before and After determine placement
The Move method accepts either a Before argument or an After argument. The value is a reference to the sheet that will be the neighbor of the moved worksheet. The argument names are part of the syntax, so keep the colon and equals sign shown in the example.
- Use
Before:=targetSheetto put the moved sheet immediately to the left of the target tab. - Use
After:=targetSheetto put it immediately to the right of the target tab. - Specify one of these arguments, not both, for a straightforward placement.
The method can also put a sheet at the beginning or end by using the first or last sheet as the target. Be attentive to the distinction between the Worksheets collection, which contains worksheet tabs, and the Sheets collection, which can also contain chart sheets. If a workbook includes chart sheets and you mean the end of the entire tab strip, refer to the last item in Sheets rather than assuming the last worksheet is the last tab.
Move a worksheet to the first or last tab
This example sends Input to the end of the workbook’s complete sheet-tab order. It checks the sheet’s index first, so it does not try to move the last tab after itself. The workbook must contain a sheet named Input.
Sub MoveInputToEnd()
Dim wb As Workbook
Dim inputSheet As Worksheet
' Include worksheet and chart-sheet tabs in the order check.
Set wb = ThisWorkbook
Set inputSheet = wb.Worksheets("Input")
If inputSheet.Index < wb.Sheets.Count Then
inputSheet.Move After:=wb.Sheets(wb.Sheets.Count)
End If
End Sub
For the first position, use the corresponding pattern with Before:=wb.Sheets(1), and check that the sheet is not already first. When there are chart sheets, this places the worksheet at the beginning of all tabs, not merely before the first worksheet. Using an explicit target makes the desired position easier to understand than relying on the current selection.
Move a worksheet to another open workbook
To transfer a worksheet, set the destination workbook and use one of its sheets as the placement target. The destination workbook must already be open, and the sheet is removed from its original workbook because this is a move, not a copy.
Sub MoveReportToArchive()
Dim sourceBook As Workbook
Dim destinationBook As Workbook
Dim reportSheet As Worksheet
' The archive workbook must already be open.
Set sourceBook = ThisWorkbook
Set destinationBook = Workbooks("Archive.xlsx")
Set reportSheet = sourceBook.Worksheets("Report")
reportSheet.Move After:=destinationBook.Sheets(destinationBook.Sheets.Count)
End Sub
This example assumes the macro is stored in the source workbook and that the open destination file is named Archive.xlsx. If the macro is stored in a different file, such as Personal.xlsb, ThisWorkbook refers to that macro file, not the workbook currently selected in Excel. In that situation, identify the intended source workbook explicitly or use ActiveWorkbook only when you have confirmed which workbook is active. Qualifying workbook and sheet references prevents a common mistake: moving a similarly named sheet from the wrong file.
Excel will not let you move the only visible sheet out of a workbook, because a workbook must retain a visible sheet. Workbook structure protection can also prevent moving tabs. Check that the destination is open, the sheet name is spelled correctly, and the source and destination are different before running a cross-workbook macro.
Choose Move or Copy
Use Move when the original tab should change position or be transferred to another workbook. Use Copy when you need to keep the original and create another sheet. The distinction matters: after a successful cross-workbook move, the source workbook no longer contains that worksheet.
For a copy within the same workbook, the analogous method is Copy, with a Before or After target. For example, a worksheet object can be copied after another worksheet. Copying may create a name such as Report (2) when a sheet with the same name already exists; Excel requires sheet names within a workbook to be unique. In either case, decide first whether formulas and reports should continue using the original tab or the new one.
Moving a sheet between workbooks can affect dependencies. Formulas, charts, names, data validation, or other workbook content may refer to the sheet being moved. Excel may adjust references or create links to the other workbook, depending on the references involved, but do not assume every dependency will remain local or work unchanged. Review formulas and links in both workbooks after a transfer, especially when the destination will be shared or renamed.
Prepare the workbook and macro safely
A small amount of preparation makes sheet-moving code more predictable and easier to recover from. Before running a procedure, check the workbook and the destination rather than assuming that the active tab or file is the one intended.
- Save a separate copy of the workbook. VBA actions cannot be undone with Ctrl+Z.
- Confirm the exact sheet names. Sheet names in the code must match the visible tab names, including spaces and punctuation.
- Confirm whether the macro uses ThisWorkbook or another workbook variable, and verify that the destination file is open.
- Check for workbook structure protection and make sure the source workbook will retain at least one visible sheet.
- After a cross-workbook move, inspect formulas, charts, defined names, and links that may depend on the transferred sheet.
Save the file as an Excel Macro-Enabled Workbook (.xlsm) if it must retain VBA code. A workbook saved as .xlsx does not preserve VBA macros. Run macros only when you trust their source; Excel’s macro security settings can block them, and enabling macros is not necessary just to open or inspect a workbook.
Common errors and how to fix them
A run-time error often comes from a missing sheet name, an incorrect workbook reference, or a move that Excel does not allow. A sheet named Sales 2025, for example, must be referred to using that exact name in the quoted string. If the workbook is protected, unprotect its structure through Excel’s protection controls if you are authorized to do so; VBA should not be used to bypass protection.
- Subscript out of range: The named workbook or worksheet cannot be found in the referenced collection. Check spelling and confirm the destination workbook is open.
- Wrong workbook changed: ThisWorkbook identifies the workbook holding the code. ActiveWorkbook identifies the workbook currently active in the Excel window, which can change while a macro runs.
- Cannot move the sheet: Check workbook structure protection and whether moving it would leave the source without a visible sheet.
- Unexpected position: Confirm whether your code targets Worksheets or Sheets. A chart sheet can make the last worksheet different from the last tab.
- Broken or external references: Review formulas and links after moving a sheet between workbooks, and keep the source file available until you verify the result.
When diagnosing a macro, test it on a copy with a simple workbook first. Use the VBA editor’s Debug menu or step through the procedure with F8 to see which line fails. This can distinguish a bad object reference from a restriction imposed by workbook protection.
Excel for Mac and Excel for the web
The basic Worksheet Move method works in desktop Excel for Windows and Mac. The code that uses ordinary workbook and worksheet objects generally does not need a different version just because the computer is a Mac. The steps for opening the editor and the available keyboard shortcuts can differ, and some Windows-specific automation or external components used by larger macros may not be supported on Mac.
Excel for the web can open a macro-enabled workbook, but it cannot run or edit VBA macros. To execute this code, open the file in desktop Excel. If you only need to move a tab manually, drag its sheet tab to the desired place; to transfer or duplicate it using the desktop interface, right-click the tab and choose Move or Copy. Menu wording and available choices can vary slightly by platform.
Frequently Asked Questions
What is the VBA command to move a worksheet?
Use the worksheet’s Move method and provide a Before or After target, such as placing one worksheet after another. Qualify the sheet with its workbook so the macro operates on the intended file.
How do I move a sheet to the end with VBA?
Use the last item in the workbook’s Sheets collection as the After target. Check that the sheet being moved is not already the last tab, and use Sheets rather than Worksheets if chart sheets should count as tabs.
Does VBA Move copy the worksheet?
No. Move relocates the existing sheet, so it leaves its original position and, when moved to another workbook, leaves the source workbook. Use the Copy method if you need to retain the original.
Can I move a worksheet to a workbook that is closed?
The destination workbook must be open for the simple method shown here, because the code refers to its Workbook object. Open the destination first, then run the macro and save both workbooks as needed.
Why does the macro say Subscript out of range?
Usually the workbook or worksheet name in the code does not match an open workbook or existing tab. Check spelling, spaces, file names, and whether the code is looking in ThisWorkbook or another workbook.
Can I run a worksheet-moving VBA macro in Excel for the web?
No. Excel for the web does not run VBA. Open the workbook in desktop Excel for Windows or Mac to run the macro; the sheet tab can also be moved manually in the web app.
Related
- Excel Named Ranges in Data Validation: How to Use Them
- Excel Unable to Remove Duplicates? Common Causes and Fixes
- Excel Text to Columns: How to Undo and Restore Data
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA UserForm Position on ScreenTo control an Excel VBA UserForm’s position, set its StartUpPosition property to 0 - Manual, then assign its…
- Excel VBA XLOOKUP: How to Use It in MacrosYes. In Excel for Microsoft 365 and supported newer versions, VBA can call XLOOKUP with Application.WorksheetFunction.XLookup. Pass it…
- VBA Copy and Paste to the Last Available RowTo copy data with VBA to the first available row after existing data, find the last occupied row…
- Excel VBA Substring: Extract, Find, and Replace TextExcel VBA has no built-in function named Substring, but you can work with part of a string using…
- VBA Range Type: What It Means and How to Use It in ExcelIn Excel VBA, Range is an object type: it represents one cell, a group of cells, or a…
- VBA Range Column: How to Reference ColumnsIn VBA, reference a whole worksheet column with Columns("C") or Range("C:C"); to get the column containing a particular…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.