Worksheet.Move method

Moves the sheet to another location in the workbook.

Syntax

expression.Move (Before, After)

expression A variable that represents a Worksheet object.

Parameters

Remarks

If you don't specify either Before or After, Microsoft Excel creates a new workbook that contains the moved sheet.

Example

Move the active sheet to the beginning

Sub MoveBeginning()
'Move the active sheet to the beginning
    ActiveSheet.Move Before:=Worksheets(1)
End Sub

In this example, we use Worksheets(1) to point to the first sheet in the workbook, and then move the active sheet before that one.

Move the active sheet to the end

Sub MoveEnd()
'Move the active sheet to the end
    ActiveSheet.Move After:=Worksheets(Worksheets.Count)
End Sub

There is nothing in VBA lets you point to “the last sheet”. But you can find the maximum count of worksheets, and then use that number as an index for the Worksheets object. In this example, you can use Worksheet(Worksheets.Count) to point to the last worksheet.

Move Sheet 1 before Sheet 3

Sub MoveBefore()
'Move Sheet 1 before Sheet 3
    Sheets("Sheet1").Move Before:=Sheets("Sheet3")
End Sub

You can move sheets simply by calling them out by sheet name. In this example, we are moving Sheet1 before Sheet3.

Move the active sheet to another Workbook

Sub MoveToSpecificWorkbook()
'Move the active sheet to the beginning of named Workbook.
'Replace YourWorkbook.xls with the full name of the target workbook you want.
    ActiveSheet.Move Before:=Workbooks("YourWorkbook.xls").Sheets(1)
End Sub

In this example, we are moving the active sheet to the beginning of Workbook named YourWorkbook.xls.

Move the active sheet to a new Workbook

Sub MoveToNew()
'Move the active sheet to a new Workbook.
    ActiveSheet.Move
End Sub