Move A Sheet

You can rearrange sheets within a workbook using the Move method with the Sheets object. When you move a sheet, you indicate the new location by specifying the name of the sheet that you want to place before or after the current sheet.

The Move method has two optional parameters, Before and After. Although both parameters are optional, you can only use one of them. Use the Before parameter to specify the sheet in front of which you want to move the current sheet. Use the After parameter to specify the sheet after which you want to place the current sheet. For example, the following code statement moves the first sheet in a workbook and places it behind the third sheet: Sheets(1). Move After:=Sheets(3) .

If you do not specify a Before or After parameter value, Excel creates a new workbook and places the moved worksheet in that workbook. The moved worksheet becomes the only worksheet in the new workbook.

When you use the Sheets object, you reference all sheets within the workbook including all workbooks, chart sheets, and macro sheets. As shown in the example, you can use index values to reference specific sheets based upon their order within the workbook. You can also reference a sheet using the sheet name that appears on the sheet tab.

Be sure to use all sheet references. Moving a sheet before or after a non-existent sheet causes VBA to display a Subscript out of range error. To avoid error, especially when using index values to reference specific sheets, consider employing the Count method to determine the exact number of sheets in the workbook before attempting to move sheets. When you know the number of sheets, you can proceed with the move by not attempting a move beyond the maximum number of sheets.

MOVE A SHEET

MOVE A SHEET

LQ Create a new subroutine.

L0 Type Dim LastSheet As Long, replacing LastSheet with the variable to determine the number of sheets in the workbook.

< Type LastSheet = Sheets.Count.

LQ Create a new subroutine.

L0 Type Dim LastSheet As Long, replacing LastSheet with the variable to determine the number of sheets in the workbook.

< Type LastSheet = Sheets.Count.

0 0

Post a comment