VBA Value Paste & PasteSpecial
In this Article
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:
1 2 |
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
1 2 |
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
1 2 |
Range("A1:B3").Copy Range("C1").PasteSpecial Paste:=xlPasteValues |
Copy and Value Paste Columns
1 2 |
Columns("A").Copy Columns("B").PasteSpecial Paste:=xlPasteValues |
Copy and Value Paste Rows
1 2 |
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:
1 2 |
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:
1 2 |
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.
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!
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
1 |
Range("A2").Value = Range("B2").Value |
You can also set a range of cells equal to a single cell’s value:
1 |
Range("A2:C5").Value = Range("A1").Value |
or a range of cells equal to another identically sized range of cells:
1 |
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.
1 |
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.
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.
1 2 |
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).
1 2 |
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.
1 2 |
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:
1 2 |
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
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):
1 2 |
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.
1 2 3 |
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
1 |
Sheets("Sheet1").Range("A1").Copy Sheets("Sheet1").Range("E1").PasteSpecial xlPasteComments |
AutoMacro | Ultimate VBA Add-in | Click for Free Trial!
Paste Special – Validation
1 2 |
Sheets("Sheet1").Range("A1:A4").Copy Sheets("Sheet1").Range("B1:B4").PasteSpecial xlPasteValidation |
Paste Special – All Using Source Theme
1 2 3 |
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
1 2 3 |
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.
1 2 3 |
Range("A1:A2").Copy Range("C1").PasteSpecial Range("E1").PasteSpecial xlPasteColumnWidths |
PasteSpecial – All MergingConditionalFormats
1 2 3 |
Range("A1:A4").Copy Range("C1").PasteSpecial Range("E1").PasteSpecial xlPasteAllMergingConditionalFormats |