Copy And Paste A Sheet

You can copy and paste the new sheets in a workbook using the Copy method with the Sheets object. When you copy a sheet, you indicate the location for the copy by specifying the name of the sheet that you want Excel to place before or after the current sheet.

The Copy 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 place the copy of the sheet. Alternately, you can use the After parameter to specify the sheet after which you want to place the copy of the sheet. The following code statement illustrates copying the first sheet in a workbook and placing the copy behind the third sheet: Sheets(1). Copy After:=Sheets(3). If you do not specify a Before or After parameter value, Excel creates a new workbook and places this lone copy in that workbook.

When you use the Sheets object you reference all sheets within the workbook, including chart sheets and macro sheets. 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 careful with the sheet references that you do use. If you try to place a copy of a sheet before or after a non-existent sheet, VBA displays a Subscript out of range error. To avoid the potential for error, especially when using index values to reference specific sheets, consider using the Count method to determine exactly how many sheets you have in a workbook before attempting to copy and paste. Knowing the number of sheets ensures that you do not attempt to place a sheet beyond the maximum number of sheets.

COPY AND PASTE A SHEET

COPY AND PASTE A SHEET

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.

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