VBA Value Paste & PasteSpecial

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on October 16, 2023

This tutorial will show you how to use PasteSpecial in VBA to paste only certain cell properties (exs. values, formats)

 

In Excel, when you copy and paste a cell you copy and paste all of the cell’s properties: values, formats, formulas, numberformatting, borders, etc:

vba copy paste special

Instead, you can “Paste Special” to only paste certain cell properties. In Excel, the Paste Special menu can be accessed with the shortcut CTRL + ALT + V (after copying a cell):

paste special vba

Here you can see all the combinations of cell properties that you can paste.

If you record a macro while using the Paste Special Menu, you can simply use the generated code. This is often the easiest way to use VBA to Paste Special.

Paste Values

Paste Values only pastes the cell “value”. If the cell contained a formula, Paste Values will paste the formula result.

This code will Copy & Paste Values for a single cell on the same worksheet:

Range("A1").Copy
Range("B1").PasteSpecial Paste:=xlPasteValues

Copy and Value Paste to Different Sheet

This example will Copy & Paste Values for single cells on different worksheets

Sheets("Sheet1").Range("A1").Copy
Sheets("Sheet2").Range("B1").PasteSpecial Paste:=xlPasteValues

These examples will Copy & Paste Values for a ranges of cells:

Copy and Value Paste Ranges

Range("A1:B3").Copy
Range("C1").PasteSpecial Paste:=xlPasteValues

Copy and Value Paste Columns

Columns("A").Copy
Columns("B").PasteSpecial Paste:=xlPasteValues

Copy and Value Paste Rows

Rows(1).Copy
Rows(2).PasteSpecial Paste:=xlPasteValues

Paste Values and Number Formats

Pasting Values will only paste the cell value. No Formatting is pasted, including Number Formatting.

Often when you Paste Values you will probably want to include the number formatting as well so your values remain formatted. Let’s look at an example.

Here we will value paste a cell containing a percentage:

vba paste values number formats

Sheets("Sheet1").Columns("D").Copy
Sheets("Sheet2").Columns("B").PasteSpecial Paste:=xlPasteValues

vba paste values

Notice how the percentage number formatting is lost and instead a sloppy decimal value is shown.

Instead let’s use Paste Values and Numbers formats:

Sheets("Sheet1").Columns("D").Copy
Sheets("Sheet2").Columns("B").PasteSpecial Paste:=xlPasteValuesAndNumberFormats

vba paste special values number formats

 

Now you can see the number formatting is also pasted over, maintaining the percentage format.

.Value instead of .Paste

Instead of Pasting Values, you could use the Value property of the Range object:

This will set A2’s cell value equal to B2’s cell value

Range("A2").Value = Range("B2").Value

You can also set a range of cells equal to a single cell’s value:

Range("A2:C5").Value = Range("A1").Value

or a range of cells equal to another identically sized range of cells:

Range("B2:D4").Value = Range("A1:C3").Value

It’s less typing to use the Value property. Also, if you want to become proficient with Excel VBA, you should be familiar with working with the Value property of cells.

Cell Value vs. Value2 Property

Technically, it’s better to use the Value2 property of a cell. Value2 is slightly faster (this only matters with extremely large calculations) and the Value property might give you a truncated result of the cell is formatted as currency or a date.  However, 99%+ of code that I’ve seen uses .Value and not .Value2.  I personally do not use .Value2, but you should be aware that it exists.

Range("A2").Value2 = Range("B2").Value2

Copy Paste Builder

We’ve created a “Copy Paste Code Builder” that makes it easy to generate VBA code to copy (or cut) and paste cells. The builder is part of our VBA Add-in: AutoMacro.

vba copy paste helper

AutoMacro also contains many other Code Generators, an extensive Code Library, and powerful Coding Tools.

VBA Programming | Code Generator does work for you!

Paste Special – Formats and Formulas

Besides Paste Values, the most common Paste Special options are Paste Formats and Paste Formulas

Paste Formats

Paste formats allows you to paste all cell formatting.

Range("A1:A10").Copy
Range("B1:B10").PasteSpecial Paste:=xlPasteFormats

Paste Formulas

Paste formulas will paste only the cell formulas. This is also extremely useful if you want to copy cell formulas, but don’t want to copy cell background colors (or other cell formatting).

Range("A1:A10").Copy
Range("B1:B10").PasteSpecial Paste:=xlPasteFormulas

Paste Formulas and Number Formats

Similar to Paste Values and Number Formats above, you can also copy and paste number formats along with formulas

vba paste special formulas

Here we will copy a cell formula with Accounting Number Formatting and Paste Formulas only.

Sheets("Sheet1").Range("D3").Copy
Sheets("Sheet2").Range("D3").PasteSpecial xlPasteFormulas

vba paste special formulas formats

Notice how the number formatting is lost and instead a sloppy non-rounded value is shown instead.

Instead let’s use Paste Formulas and Numbers formats:

Sheets("Sheet1").Range("D3").Copy
Sheets("Sheet2").Range("D3").PasteSpecial xlPasteFormulasAndNumberFormats

vba paste special formulas numberformatting

Now you can see the number formatting is also pasted over, maintaining the Accounting format.

Paste Special – Transpose and Skip Blanks

AutoMacro | Ultimate VBA Add-in | Click for Free Trial!

Paste Special – Transpose

Paste Special Transpose allows you to copy and paste cells changing the orientation from top-bottom to left-right (or vis-a-versa):

 

Sheets("Sheet1").Range("A1:A5").Copy
Sheets("Sheet1").Range("B1").PasteSpecial Transpose:=True

vba paste special transpose

vba transpose

Paste Special – Skip Blanks

Skip blanks is a paste special option that doesn’t seem to be used as often as it should be.  It allows you to copy only non-blank cells when copying and pasting. So blank cells are not copied.

In this example below. We will copy column A, do a regular paste in column B and skip blanks paste in column C. You can see the blank cells were not pasted into column C in the image below.

Sheets("Sheet1").Range("A1:A5").Copy
Sheets("Sheet1").Range("B1").PasteSpecial SkipBlanks:=False
Sheets("Sheet1").Range("C1").PasteSpecial SkipBlanks:=True

vba value paste skip blanks

vba skip blanks

Other Paste Special Options

Paste Special – Comments

Sheets("Sheet1").Range("A1").Copy Sheets("Sheet1").Range("E1").PasteSpecial xlPasteComments

vba paste special comments

vba paste comments

AutoMacro | Ultimate VBA Add-in | Click for Free Trial!

Paste Special – Validation

vba paste special validation

Sheets("Sheet1").Range("A1:A4").Copy
Sheets("Sheet1").Range("B1:B4").PasteSpecial xlPasteValidation

vba paste validation

Paste Special – All Using Source Theme

vba paste special allusingsourcetheme

Workbooks(1).Sheets("Sheet1").Range("A1:A2").Copy
Workbooks(2).Sheets("Sheet1").Range("A1").PasteSpecial
Workbooks(2).Sheets("Sheet1").Range("B1").PasteSpecial xlPasteAllUsingSourceTheme

Paste Special – All Except Borders

Range("B2:C3").Copy
Range("E2").PasteSpecial
Range("H2").PasteSpecial xlPasteAllExceptBorders

vba paste special allexceptborders

PasteSpecial – Column Widths

A personal favorite of mine. PasteSpecial Column Widths will copy and paste the width of columns.

Range("A1:A2").Copy
Range("C1").PasteSpecial
Range("E1").PasteSpecial xlPasteColumnWidths

vba paste special column widths

PasteSpecial – All MergingConditionalFormats

vba paste special all merging conditional formats

Range("A1:A4").Copy
Range("C1").PasteSpecial
Range("E1").PasteSpecial xlPasteAllMergingConditionalFormats

vba paste special formats

 

 

vba-free-addin

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)

Free Download

Return to VBA Code Examples