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.


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:



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


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:



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:



The source cell contains 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: