[Excel VBA] How to Move and Reorder Worksheets (.Move)

2025年12月26日

When creating a new report sheet using VBA, you often want to organize the workbook by moving that sheet to the far left (beginning) or far right (end) so users can find it easily.

While you can change the order of sheets manually by dragging and dropping, you can automate this sorting process using the VBA .Move method. In this article, I will explain the basic ways to move sheets to specific positions using the .Move method.

Basics of the Worksheet.Move Method

The .Move method is a command used to move a specified sheet to a different location.

SheetToMove.Move Before:=ReferenceSheet OR SheetToMove.Move After:=ReferenceSheet

Before argument: Moves the sheet before (to the left of) the specified sheet.
After argument: Moves the sheet after (to the right of) the specified sheet.

Note that you can specify either Before or After, but not both.

VBA Code Examples (Practical Use Cases)

Here are code examples for the three most common movement patterns.

1. Moving a Sheet to the Beginning of the Workbook

Sub MoveSheetToBeginning()
    Dim sheetToMove As Worksheet
    
    ' Specify the sheet to move by name (e.g., "Report")
    Set sheetToMove = ThisWorkbook.Worksheets("Report")
    
    ' Move before the first sheet in the workbook
    sheetToMove.Move Before:=ThisWorkbook.Worksheets(1)
    
    MsgBox "Moved '" & sheetToMove.Name & "' to the beginning of the workbook."
End Sub

Since Worksheets(1) refers to the sheet at the far left of the workbook, moving a sheet Before it places the sheet at the very beginning.

2. Moving a Sheet to the End of the Workbook

Sub MoveSheetToEnd()
    Dim sheetToMove As Worksheet
    
    ' Specify the sheet to move by name (e.g., "Summary")
    Set sheetToMove = ThisWorkbook.Worksheets("Summary")
    
    ' Move after the last sheet in the workbook
    sheetToMove.Move After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
    
    MsgBox "Moved '" & sheetToMove.Name & "' to the end of the workbook."
End Sub

We use Worksheets.Count to get the total number of sheets and identify the last sheet using Worksheets(Worksheets.Count). Moving a sheet After this places it at the very end.

3. Moving Next to a Specific Sheet

Sub MoveSheetNextTo()
    Dim sheetToMove As Worksheet
    Dim referenceSheet As Worksheet
    
    ' Specify the sheet to move and the reference sheet
    Set sheetToMove = ThisWorkbook.Worksheets("Appendix")
    Set referenceSheet = ThisWorkbook.Worksheets("MainData")
    
    ' Move the "Appendix" sheet after the "MainData" sheet
    sheetToMove.Move After:=referenceSheet
    
    MsgBox "Moved '" & sheetToMove.Name & "' next to '" & referenceSheet.Name & "'."
End Sub

Key Points and Warnings

Specifying the Target Sheet

It is safest and most reliable to specify the sheet you want to move by name, like Worksheets("SheetName").

Specifying the Destination

Use the Before or After argument to clearly indicate the destination. After the argument, specify the reference sheet object (e.g., Worksheets(1) or Worksheets("Data")).

[Important] What Happens if Arguments are Omitted?

If you omit both Before and After arguments and run code like Worksheets("Report").Move, the “Report” sheet will be moved to a new workbook and removed from the original workbook.

This is a useful feature if you want to extract a sheet as a standalone file, but be careful not to execute it unintentionally, as it changes the structure of your workbook.

Summary

To change the order of sheets in VBA, use the .Move method.

Move to Beginning: mySheet.Move Before:=Worksheets(1)
Move to End: mySheet.Move After:=Worksheets(Worksheets.Count)
Move Next to Specific Sheet: mySheet.Move After:=Worksheets("ReferenceSheet")

By adding this process after automatically generating report sheets with macros, you can organize the workbook structure and provide a file that is easy for users to understand.

よかったらシェアしてね！