Copy Method

Switch View

Copy Method

Collapse All

Copy Method

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

Copy method as it applies to the Range object.

Copy method as it applies to the PivotTable object.

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.