.
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 |
AllPlusRowHeightAndColumnWidth | CopyComments, CopyFormatting, CopyFormulas, CopyMergedCells, CopyValues, CopyColumnWidth and CopyRowHeight |
FormulasAndFormatting | CopyFormulas and CopyFormatting |
ValuesAndFormatting | CopyValues and CopyFormatting |
ValuesFormulasAndFormatting | CopyValues, CopyFormulas and CopyFormatting |
ValuesOnly | CopyValues |
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.