Move Worksheets

Basics: Moving with the Move Method

Use the Move method to move a sheet, and specify the destination with Before (in front of a sheet) or After (behind a sheet).

Public Sub MoveSheet()
    ThisWorkbook.Worksheets("Sheet1").Move After:=ThisWorkbook.Worksheets("Sheet3")
End Sub

Moving to the Start or End

Public Sub MoveSheetToStart()
    With ThisWorkbook
        .Worksheets("Sheet1").Move Before:=.Worksheets(1)
    End With
End Sub

Public Sub MoveSheetToEnd()
    With ThisWorkbook
        .Worksheets("Sheet1").Move After:=.Worksheets(.Worksheets.Count)
    End With
End Sub

Worksheets.Count is the number of worksheets. If you want to place the sheet after chart sheets as well, use Sheets instead of Worksheets.

Moving Multiple Sheets at Once

Pass sheet names as an array with the Array function to move several sheets in one call.

Public Sub MoveMultipleSheets()
    With ThisWorkbook
        .Worksheets(Array("Sheet1", "Sheet2")).Move After:=.Worksheets("Sheet4")
    End With
End Sub

Specifying multiple sheets works the same way as when selecting them.

Checking That the Sheets Exist

Specifying a sheet name that does not exist raises run-time error 9 (“Subscript out of range”). Checking beforehand is safer.

Public Sub SafeMoveSheet()
    If Not SheetExists("Sheet1") Or Not SheetExists("Sheet3") Then
        MsgBox "The source or destination sheet does not exist."
        Exit Sub
    End If

    ThisWorkbook.Worksheets("Sheet1").Move After:=ThisWorkbook.Worksheets("Sheet3")
End Sub

Private Function SheetExists(ByVal sheetName As String) As Boolean
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = sheetName Then
            SheetExists = True
            Exit Function
        End If
    Next ws
End Function

Moving to Another Workbook

Specify a sheet in another workbook for Before / After to move the sheet into that workbook.

Public Sub MoveSheetToOtherWorkbook()
    Dim destination As Workbook
    Set destination = Workbooks("Report.xlsx") ' an open workbook

    With destination
        ThisWorkbook.Worksheets("Sheet1").Move After:=.Worksheets(.Worksheets.Count)
    End With
End Sub

The moved sheet is removed from the original workbook. If you want to keep the original, use the Copy method instead.

Things to Watch Out For

• Formula references: When you move a sheet to another workbook, formulas referring to other sheets may become external references.
• Protection: If the workbook structure is protected, Move raises an error.
• At least one sheet must remain: You cannot empty a workbook by moving away all of its visible sheets.

Summary

• Move a sheet with the Move method and Before or After
• Omitting both moves the sheet into a new workbook, so be careful
• Multiple sheets can be specified at once with Array
• A non-existent sheet name causes error 9, so check beforehand