Move Worksheets

Moving Sheets with VBA Code

Below, I’ll explain how to move specific sheets using VBA.

Basic Movement Method

First, let’s look at how to move a sheet with basic code.

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

In this example, Sheet1 is moved after Sheet3.

Moving Sheets to the Beginning or End

You can also move a sheet to the beginning or end of the workbook.

Moving a sheet to the beginning:

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

Moving a sheet to the end:

Sub MoveSheetToEnd()
    ThisWorkbook.Sheets("Sheet1").Move After:=ThisWorkbook.Sheets(Sheets.Count)
End Sub

Moving Multiple Sheets

When moving multiple sheets simultaneously, specify the sheets as an array.

Sub MoveMultipleSheets()
    Sheets(Array("Sheet1", "Sheet2")).Move After:=Sheets("Sheet4")
End Sub

This code moves Sheet1 and Sheet2 after Sheet4.

For more details on selecting multiple sheets, please refer to the following page:

Handling Cases Where the Destination Sheet Doesn’t Exist

When moving sheets in VBA, an error will occur if the specified destination sheet doesn’t exist. It’s important to incorporate error handling to prevent errors.

Sub SafeMoveSheet()
    On Error Resume Next
    If Not SheetExists("Sheet3") Then
        MsgBox "Sheet3 does not exist."
        Exit Sub
    End If
    On Error GoTo 0

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

Function SheetExists(sheetName As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets(sheetName)
    On Error GoTo 0
    SheetExists = Not ws Is Nothing
End Function

This code checks if Sheet3 exists, and if it doesn’t, displays a message and terminates the process.

Benefits of Moving Worksheets

Moving worksheets using VBA has the following benefits:

• Efficiency through automation: Eliminates the effort of manually moving sheets.
• Organized workbook: By rearranging sheets in the required order, the readability of the workbook improves.
• Reduced repetitive tasks: When sheets need to be moved regularly, automating with VBA saves time.

Summary

Using VBA, you can automate the movement of worksheets and streamline your daily tasks. We’ve learned the basic methods for moving sheets, as well as moving multiple sheets and error handling. Take this opportunity to leverage VBA and achieve more efficient Excel work.