Excel / VBA PasteSpecial Method: Explained with 3 Examples

VBA PasteSpecial Tutorial Featured image

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:

vba-pasteSpecial

You can see in the above code that 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 were 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:

vba-pasteSpecial-form

You can see, the background/interior 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:

vba-paste-formulas

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

What paste options are provided?

Following XlPasteType are available:

Name Value
xlPasteAll -4104
xlPasteAllExceptBorders 7
xlPasteAllMergingConditionalFormats 14
xlPasteAllUsingSourceTheme 13
xlPasteColumnWidths 8
xlPasteComments -4144
xlPasteFormats -4122
xlPasteFormulas -4123
xlPasteFormulasAndNumberFormats 11
xlPasteValidation 6
xlPasteValues -4163
xlPasteValuesAndNumberFormats 12

 

Learn more about each option here:

https://learn.microsoft.com/en-us/office/vba/api/excel.xlpastetype