Copy Method

Article
07/11/2006

Copy method as it applies to the Sheets, Worksheet, and Worksheets objects.

Copies the specified sheet to another location in the workbook.

expression.Copy(Before, After)

*expression * Required. An expression that returns one of the above objects.

Before Optional Variant. The sheet before which the copied sheet will be placed. You cannot specify Before if you specify After.

After Optional Variant. The sheet before which the copied sheet will be placed. You cannot specify Before if you specify After.

Copy method as it applies to the Range object.

Copies the range to the cell location specified in the Destination argument. If you omit the Destination argument, the range is copied to the Clipboard.

expression.Copy(Destination)

*expression * Required. An expression that returns one of the above objects.

Destination Optional Variant. Specifies the new range to which the specified range will be copied.

Copy method as it applies to the PivotTable object.

Copies the PivotTable object to the Windows Clipboard.

expression.Copy(Selection)

*expression * Required. An expression that returns a PivotTable object.

Selection Optional Object. The specified selection.

Example

As it applies to the Sheets, Worksheet, and Sheets objects.

This example makes a copy of Sheet1 so that it appears at the end of the worksheet list.

Sub CopySheet()

   ' Copy Sheet1.
   Spreadsheet1.Sheets("Sheet1").Copy  , _
      Spreadsheet1.Sheets(Spreadsheet1.Sheets.Count)

   ' Rename the new copy of Sheet1.
   Spreadsheet1.ActiveSheet.Name = "Copy of Sheet1"

End Sub

As it applies to the Range object.

This example copies cells A1:B10 of Sheet1 to a range beginning at the first blank cell in column A of Sheet2.

Sub CopyCells()

   Dim ssConstants
   Dim rngDest

   Set ssConstants = Spreadsheet1.Constants

   ' Set a variable to the first blank cell in column A of Sheet2.
   Set rngDest = Spreadsheet1.Sheets("Sheet2").Range("A262144").End(ssConstants.xlUp).Offset(1, 0)

   ' Copy cell2 A1:B10 of Sheet1 to the first blank cell in column A of Sheet2.
   Spreadsheet1.Sheets("Sheet1").Range("A1:B10").Copy rngDest

End Sub

As it applies to the PivotTable object.

This example copies the active view of PivotTable1 to a new Microsoft Excel worksheet, prints the worksheet, and then closes Excel. Sub Copy_To_XL() Dim xlApp Dim XlBook ' Create a new instance of Excel. Set xlApp = CreateObject("Excel.Application.11") ' Create a new workbook. Set xlBook = xlApp.Workbooks.Add ' Copy the current view of the PivotTable. PivotTable1.Copy PivotTable1.ActiveView ' Paste the PivotTable. xlApp.ActiveSheet.Paste ' Format the columns. xlApp.Selection.Columns.AutoFit ' Print the PivotTable. xlApp.ActiveWindow.SelectedSheets.PrintOut 1 ' Close the workbook. xlBook.Close False ' Exit Excel. xlApp.Quit ' Clean up variables. Set xlBook = Nothing Set xlApp = Nothing End Sub

See Also | Cut Method | Paste Method

Share via