VBA Value Paste & PasteSpecial

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:

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):

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:

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

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

 

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.

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

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

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

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

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

Paste Special – Transpose and Skip Blanks

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

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

Other Paste Special Options

Paste Special – Comments

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

Paste Special – Validation

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

Paste Special – All Using Source Theme

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

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

PasteSpecial – All MergingConditionalFormats

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