Excel VBA Range PasteSpecial Method

This Excel VBA tutorial explains how to use Range.PasteSpecial Method to paste special such as paste values.

Excel VBA Range PasteSpecial Method

In Excel worksheet, if you copy a Cell and then Paste Special, you can see a list of options. The most commonly used Paste Special is Paste Values, in order to remove all the formula.

In Excel VBA, Paste Speical is done through Range.PasteSpecial Method.

Syntax of Range.PasteSpecial Method

Range.PasteSpecial(Paste, Operation, SkipBlanks, Transpose)

Name	Required/Optional	Description
Paste	Optional	Name Description xlPasteAll Everything will be pasted. xlPasteAllExceptBorders Everything except borders will be pasted. xlPasteAllMergingConditionalFormats Everything will be pasted and conditional formats will be merged. xlPasteAllUsingSourceTheme Everything will be pasted using the source theme. xlPasteColumnWidths Copied column width is pasted. xlPasteComments Comments are pasted. xlPasteFormats Copied source format is pasted. xlPasteFormulas Formulas are pasted. xlPasteFormulasAndNumberFormats Formulas and Number formats are pasted. xlPasteValidation Validations are pasted. xlPasteValues Values are pasted. xlPasteValuesAndNumberFormats Values and Number formats are pasted.
Operation	Optional	Name Description xlPasteSpecialOperationAdd Copied data will be added with the value in the destination cell. xlPasteSpecialOperationDivide Copied data will be divided with the value in the destination cell. xlPasteSpecialOperationMultiply Copied data will be multiplied with the value in the destination cell. xlPasteSpecialOperationNone No calculation will be done in the paste operation. xlPasteSpecialOperationSubtract Copied data will be subtracted with the value in the destination cell.
SkipBlanks	Optional	True to have blank cells in the range on the Clipboard not be pasted into the destination range. The default value is False.
Transpose	Optional	True to transpose rows and columns when the range is pasted.The default value is False.

Example 1 – Paste Value

The below VBA copy the whole active worksheet and paste as value.

Public Sub pasteVal()
    ActiveSheet.Cells.Copy
    ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues
End Sub

Example 2 – Multiply

Suppose we want to multiply Range A1:C2 by 2.

Public Sub pasteMultiply()
    Range("E1").Copy  'Suppose E1 contains value 2
    Range("A1:C2").PasteSpecial Operation:=xlPasteSpecialOperationMultiply
End Sub

Result

 

Copy and Paste in one line

Incidentally, if you just want to copy and paste normally, you can use Copy Method with Destination argument.

The below example copy Range A1:C2 and paste in A10.

Public Sub cpy()
    Range("A1:C2").Copy Destination:=Range("A10")
End Sub