VBA PasteSpecial Method of Range Object is used to pastes a range from clipboard to the specified range in the worksheet.

VBA PasteSpecial Method Excel Range Object

VBA PasteSpecial method of Range – Syntax

Here is the syntax to VBA PasteSpecial method of range object .You can use the ‘Range.PasteSpecial’ method to do PasteSpecial for the specified range.

Range(“YourRange”).PasteSpecial(

[Paste As XlPasteType = xlPasteAll], [Operation As XlPasteSpecialOperation = xlPasteSpecialOperationNone], [SkipBlanks], [Transpose])

Here,
Paste:It is Optional argument. Paste specifies the part of the range to be pasted. Please find the ‘xlPasteType’ table at the bottom of the page for more information.
Operation:It is Optional argument. Operation specifies the how numeric data will be changed at the destination cells in the worksheet. Please find the ‘XlPasteSpecialOperation’ table at the bottom of the page for more information.
SkipBlanks:It is Optional argument. SkipBlanks contains boolean value either True or False. The default value is False. If SkipBlanks is True, It won’t copy the blank cells from the clipboard(Which we copied to dp pastespecial) to the destination range.
Transpose:It is Optional argument. Transpose contains boolean value either True or False. The default value is False. If Transpose is True, It will transpose the rows and columns when the range pasted.

VBA PasteSpecial method of Range – Example1

Here is the example for PasteSpecial method of range object to pastes a range from the clipboard to the specified range in the worksheet. Here you can use ‘Range.PasteSpecial’ method of range object.

Sub Range_PasteSpecial_Values()
Range("C6:D11").Copy
Range("G6").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

In the above example, we are copying the range(“C6:D11”) to the clip board. And the pasting the only values to the range G6 from the copied range.

Excel Paste Special Enumerations

Here are the available enumerations while copying and pasting the data using PasteSpecial method of Excel Range. Excel Paste Type (XlPasteType) and Excel Paste Operations (XlPasteSpecialOperation) helps to copy the data from source range to destination range in different situation with verity of criteria (based on our requirement).

Excel Paste Special Types- Enumerations

Here are the available enumerations for Excel Paste Type (XlPasteType): This will tell what content from the source range (copied range) to be pasted in the target or destination range.

Enum NameEnum ValueDescription
xlPasteValues-4163When XlPasteType = xlPasteValues, Only values in the copied range are pasted.
xlPasteComments-4144When XlPasteType = xlPasteComments, Only Comments in the copied range are pasted.
xlPasteFormulas-4123When XlPasteType = xlPasteFormulas, Only Formulas in the copied range are pasted.
xlPasteFormats-4122When XlPasteType = xlPasteFormats, Only format of the copied Range is pasted.
xlPasteAll-4104When XlPasteType = xlPasteAll, default and everything from the copied range will be pasted.
xlPasteValidation6When XlPasteType = xlPasteValidation, Only Validations of the copied range are pasted.
xlPasteAllExceptBorders7When XlPasteType = xlPasteAllExceptBorders, Everything from the copied range will be pasted, except the borders.
xlPasteColumnWidths8When XlPasteType = xlPasteColumnWidths, Only column width of the copied range is pasted.
xlPasteFormulasAndNumberFormats11When XlPasteType = xlPasteFormulasAndNumberFormats, Only Formulas and Number formats are pasted.
xlPasteValuesAndNumberFormats12When XlPasteType = xlPasteValuesAndNumberFormats, Only Values and Number formats are pasted.
xlPasteAllUsingSourceTheme13When XlPasteType = xlPasteAllUsingSourceTheme, Everything will be pasted using the copied range theme.
xlPasteAllMergingConditionalFormats14When XlPasteType = xlPasteAllMergingConditionalFormats, Everything will be pasted and conditional formats of the source and destination ranges will be merged .

Excel Paste Special Operations – Enumerations

Here are the available enumerations for Excel Paste Special Operations (XlPasteSpecialOperation ): This will be different arithmetic operations on the destination range:

Enum NameEnum ValueDescription
xlPasteSpecialOperationAdd2When XlPasteSpecialOperation = xlPasteSpecialOperationAdd, addition operation will be performed on the destination range. i.e; copied value will be added to the values in the destination range.
xlPasteSpecialOperationSubtract3When XlPasteSpecialOperation = xlPasteSpecialOperationSubtract, substraction operation will be performed on the destination range. i.e; copied value will be subtracted from the values in the destination range.
xlPasteSpecialOperationMultiply4When XlPasteSpecialOperation = xlPasteSpecialOperationMultiply, mltiplicaation operation will be performed on the destination range. i.e; copied value will be multiplied to the values in the destination range.
xlPasteSpecialOperationDivide5When XlPasteSpecialOperation = xlPasteSpecialOperationDivide, Divide operation will be performed on the destination range. i.e; copied value will be divided the values in the range cell.
xlPasteSpecialOperationNone-4142When XlPasteSpecialOperation = xlPasteSpecialOperationNone, default and no calculations will be pefromed when pasted.