Return to VBA Code Examples

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:

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:

Copy and Value Paste to Different Sheet

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

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

Copy and Value Paste Ranges

Copy and Value Paste Columns

Copy and Value Paste Rows

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

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:

vba paste special values number formats

 

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

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! automacro

Learn More!!

.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

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

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

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.

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.

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

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.

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:

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

 

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.

vba value paste skip blanks

vba skip blanks

Other Paste Special Options

Paste Special – Comments

vba paste special comments

vba paste comments

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

Paste Special – Validation

vba paste special validation

vba paste validation

Paste Special – All Using Source Theme

vba paste special allusingsourcetheme

Paste Special – All Except Borders

vba paste special allexceptborders

PasteSpecial – Column Widths

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

vba paste special column widths

PasteSpecial – All MergingConditionalFormats

vba paste special all merging conditional formats

vba paste special formats