VBA Move Worksheet Explained with Examples

Move worksheet in VBA is used to move the worksheet(s) from one location to another location in the same workbook or another new workbook or existing workbook. Here Move is method of worksheet object. Sometimes we may want to move worksheet in the active workbook at the beginning of the worksheet or in between worksheets or at the end of worksheet. Need basis we can move the worksheets using Move worksheet method in VBA.

In this Topic:

• VBA Move Worksheet: Syntax
• VBA Move Worksheet: Using Before
• VBA Move Worksheet: Using After
• VBA Move Worksheet: Before Specified Worksheet
• VBA Move Worksheet: To New Workbook
• VBA Move Worksheet: To Specific Workbook
• VBA Move Worksheet: Instructions

VBA Move Worksheet: Syntax

Please find the below syntax to Move Worksheet at the beginning of the Worksheet or In-between Worksheet’s or end of the Worksheet using VBA.

Worksheets(“Worksheet Name”).Move([Before], [After])

Where
Before: It’s an Optional parameter. The worksheet will be moved to before the specified worksheet. Then we can’t specify after parameter.
After: It’s an Optional parameter. The worksheet will be moved to after the specified worksheet. Then we can’t specify after parameter.

Note: If we don’t specify Optional parameter either Before or After, Excel will create new workbook and it contains moved Worksheet.

VBA Move Worksheet: Using Before

Please find the below , It will show you how to Move the Worksheet to the beginning.

Sub MoveSheet_Beginning()
    Worksheets("Sheet3").Move Before:=Worksheets(1)
End Sub

In the above we are moving the Worksheet named ‘Sheet3’ to the beginning of the worksheet. Where ‘1’ represents the Worksheet index number (Nothing but first available sheet in the workbook).

Sub MoveSheet_Beginning1()
    ActiveSheet.Move Before:=Worksheets(1)
End Sub

In the above we are moving the active worksheet to the beginning of the worksheet.

VBA Move Worksheet: Using After

Please find the below , It will show you how to Move the Worksheet at the end of the available worksheets.

Sub MoveSheet_End()
    Worksheets("Sheet3").Move After:=Worksheets(Worksheets.Count)
End Sub

In the above we are moving the Worksheet named ‘Sheet3’ to the end of the worksheet. Where ‘Worksheets.Count’ represents the number of available worksheets in the workbook

Sub MoveSheet_End()
    ActiveSheet.Move After:=Worksheets(Worksheets.Count)
End Sub

In the above we are moving the active worksheet to the end of the worksheet.

VBA Move Worksheet: Before Specified Worksheet

Please find the below example , It will show you how to Move the Worksheet either before or after the specified worksheet.

Sub MoveSheet_Before()
    Worksheets("Sheet2").Move Before:=Sheets("Sheet5")
End Sub

Please find the above , we are moving ‘Sheet2’ to the before ‘Sheet5’.

Sub MoveSheet_Before()
    ActiveSheet.Move Before:=Sheets("Sheet5")
End Sub

In the above , we are moving active sheet to the before ‘Sheet5’.

Sub MoveSheet_After()
    Worksheets("Sheet2").Move After:=Sheets("Sheet5")
End Sub

Please find the above , we are moving ‘Sheet2’ to the after ‘Sheet5’.

Sub MoveSheet_After()
    ActiveSheet.Move After:=Sheets("Sheet5")
End Sub

In the above , we are moving active sheet to the after ‘Sheet5’.

VBA Move Worksheet: To New Workbook

Please find the below , It will show you how to Move the Worksheet named ‘Sheet1’ to new workbook.

Sub MoveSheet_NewWorkbook()
    Sheets("Sheet1").Move
End Sub

Please find the below , It will move the active worksheet to new workbook.

Sub MoveSheet_NewWorkbook()
    ActiveSheet.Move
End Sub

VBA Move Worksheet: To Specific Workbook

Please find the below , It will show you how to Move the Worksheet named ‘Sheet1’ to Specific workbook before ‘Sheet3’.

Sub MoveSheet_SpecificWorkbook ()
    Sheets("Sheet1").Move Before:=Workbooks("YourWorkbookName.xls").Sheets(“Sheet3”)
End Sub

Please find the below , It will move the active worksheet to Specific workbook after ‘Sheet3’.

Sub MoveSheet_SpecificWorkbook ()
    ActiveSheet.Move After:=Workbooks("YourWorkbookName.xls"). Sheets(“Sheet3”)
End Sub

VBA Move Worksheet: Instructions

Please follow the below step by step instructions to execute the above mentioned VBA macros or codes:
Step 1: Open an Excel Worksheet
Step 2: Press Alt+F11 to Open VBA Editor
Step 3: Insert a Module from Insert Menu
Step 4: Copy the above code for activating a range and Paste in the code window (VBA Editor)
Step 5: Save the file as macro enabled Worksheet
Step 6: Press ‘F5′ to run it or Keep Pressing ‘F8′ to debug the code line by line and have a look how the Worksheet(s) moving in the workbook.