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.
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.
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|
|*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".|
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.