Worksheet.Move method

ExcelBaby May 7, 2022 3 minutes to read

48 0

Moves the sheet to another location in the workbook.

Syntax

expression.Move (Before, After)

expression A variable that represents a Worksheet object.

Parameters

Name	Required/Optional	Data type	Description
Before	Optional	Variant	The sheet before which the moved sheet will be placed. You cannot specify Before if you specify After.
After	Optional	Variant	The sheet after which the moved sheet will be placed. You cannot specify After if you specify Before.

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