Excel / VBA PasteSpecial Method: Explained with 3 Examples

PasteSpecial function in VBA

Normally, as you copy/paste a cell or range of cells in Excel, it pastes value, formatting (font color, interior color, italic, bold, etc.), Excel formulas, comments, etc. in the destination cells.

In certain scenarios, you may need to only paste values to the destination cell rather than font color, backgrounds, or formulas.

This is where the PasteSpecial option works in Excel as well as VBA macros.

Syntax:

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

We will show you how to use the PasteSpecical function in VBA code with various arguments and paste options.

An example of pasting only values

Our sample sheet for this example contains cells with interior color. Whereas, the text color of a few cells is red and blue.

By using PasteSpecial function, we will only copy/paste values to the destination cells in the same sheet.

VBA code:

Sub pasteSpe_ex()

Dim rng_paste As Range

Dim x

Set rng_paste = Range("B5:D9")

rng_paste.Copy

'Pasting only values to the destination range

Range("G10:I15").PasteSpecial Paste:=xlPasteValues

End Sub

Result:

You can see in the above code, after using the range method in the source range, we used PasteSpecial function with the Paste argument.

Paste:=xlPasteValues

It enabled us to copy only values while font and interior colors are not pasted.

Copy only formatting example

This time we will only copy formatting to the destination range. Values are not copied. Have a look at the same sheet we used above:

Sub pasteSpe_ex()

Dim rng_paste As Range

Set rng_paste = Range("B5:D9")

rng_paste.Copy

'use xlPasteFormats for copying formats only

Range("G10:I15").PasteSpecial Paste:=xlPasteFormats

End Sub

Output:

You can see, background/ineterior colors of the destination cells are the same as the source.

For showing the font color, we wrote some text in the pasted cells and you see it’s also copied.

Pasting formulas only

For this example, we have another sheet that contains the VLOOKUP formula in the E14 cell.

We will copy it to the G16 cell and our task is to only copy the formula of the cell – no formatting or text.

Have a look at the code and output:

Sub pasteSpe_ex()

Dim rng_paste As Range

Set rng_paste = Range("D14")

rng_paste.Copy

'use xlPasteFormulas to copy formula only

Range("F14").PasteSpecial Paste:=xlPasteFormulas

End Sub

Result:

The source cell contains VLOOKUP formula, and has interior color, bold, italic, and underlined – however, the destination cell only contains the formula.