Moves the sheet to another location in the workbook.
expression.Move (Before, After)
expression A variable that represents a Worksheet object.
|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.|
If you don't specify either Before or After, Microsoft Excel creates a new workbook that contains the moved sheet.
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