VBA PasteSpecial Method of Range Object Explained with Examples

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

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.

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: