Skip to end of metadata
Go to start of metadata
ExcelApplication's CopyPaste method allows you to copy data from an external worksheet. It can also be used to copy data from a worksheet within the same workbook.

CopyPaste is a method of the Worksheet object and should be called from the destination worksheet. CopyPaste uses a CopyPasteProperties object to store information on precisely how data should be copied. Options include: values, formulas, formatting, merged cells, comments, height and width.

Step 1: Setting up your code to copy from an external worksheet:

In the code below, a source worksheet is opened and a destination worksheet is created. The CopyPaste method takes an Area object to represent the area to be copied, so this is created first.

Step 2: Creating a CopyPasteProperties object

Now that the data to be copied is mapped to an Area object, a CopyProperties object should be created from the destination workbook. The CopyProperties object is used to specify precisely what within the area should be copied. In the code below, only the values will be copied.

The ValuesOnly type is a convenience type. The same result could be achieved by using the None type and adding a second line of code where the CopyValues property was set to true.

Using the None type allows you to set Copy properties individually. The available properties include:

Besides None, other CopyPasteType values are available, saving you unnecessary lines of code:

CopyPasteType

Sets the following CopyPasteProperty values to true

None

No CopyPasteProperty values set.

All

CopyComments, CopyFormatting, CopyFormulas, CopyMergedCells and CopyValues
Note: CopyColumnWidth and CopyRowHeight are set to false to avoid unexpected results. 

AllPlusRowHeightAndColumnWidth

CopyComments, CopyFormatting, CopyFormulas, CopyMergedCells, CopyValues, CopyColumnWidth and CopyRowHeight

FormulasAndFormatting

CopyFormulas and CopyFormatting

ValuesAndFormatting

CopyValues and CopyFormatting

ValuesFormulasAndFormatting

CopyValues, CopyFormulas and CopyFormatting

ValuesOnly

CopyValues

Icon

Optionally, if you intend to use the CopyPasteType of All, you can save even more lines of code by using CopyPaste method without the properties object parameter. Omission of the properties object parameter will result in the assumption of the value of "All".

Step 3: Calling the CopyPaste method from the Destination Worksheet

To complete the code sample that is being built, only the CopyPaste method now needs to be called. You will pass it the address of the cell that represents the upper left corner of the location to paste and the Area and CopyPasteProperties objects that were created above.

 

 

  • No labels