🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

VBA PasteSpecial in Excel — Paste Only Values, Formats or Formulas (and Why It Fails)

|

VBA PasteSpecial in Excel — Paste Only Values, Formats or Formulas (and Why It Fails)

TL;DR — PasteSpecial is a filtered paste: you .Copy a range to the clipboard, then choose which single layer crosses — values, formats, formulas, or column widths — and, optionally, an operation (add, multiply, transpose). The catch is that it reads a live clipboard, so it must follow a .Copy immediately; anything that clears the clipboard in between — a MsgBox, another copy, even Excel losing focus — gives you PasteSpecial method of Range class failed. If you are copying everything as-is, you do not need PasteSpecial at all — that is what Copy Destination is for.

' PasteSpecial reads the clipboard, so Copy immediately before it.
Range("A1:A20").Copy                                ' load the clipboard
Range("C1").PasteSpecial Paste:=xlPasteValues       ' only the values cross
Application.CutCopyMode = False                      ' clear the marching ants

' Choose what crosses AND how it lands in one call:
Range("E1:E20").Copy
Range("G1").PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlPasteSpecialOperationAdd, Transpose:=False

PasteSpecial is the code twin of the Paste Special dialog — the one that lets you paste values only, formats only, or transpose a column into a row. Plain Copy Paste moves the whole cell; PasteSpecial is what you reach for when you want one layer of it and nothing else. That power comes with one hard constraint that trips up almost everyone the first time, so we start there and build out.

What you'll learn

  • The mental model — PasteSpecial is a filter over a paste, not a different kind of copy
  • The one rule that prevents most failures — it reads a live clipboard, so .Copy must come right before
  • The two axes — what crosses (Paste:=) and how it lands (Operation:=, Transpose:=)
  • The values-only paste and its format trap (xlPasteValues vs xlPasteValuesAndNumberFormats)
  • Cleaning up with Application.CutCopyMode = False
  • When PasteSpecial is the wrong tool and a direct move is better

The mental model: a filter over a paste

A plain paste copies a cell whole — its value, its formula, its number format, its borders, its fill, all at once. PasteSpecial puts a filter in front of that paste. You still .Copy the entire source to the clipboard, but at the moment of pasting you say "let only this one layer through." Think of the clipboard as a stack of transparent sheets — value, formula, format, column width — and PasteSpecial as the one tool that lets you lift off a single sheet and lay it down on the target.

That is the whole idea, and it explains why PasteSpecial always comes in two halves: a .Copy that loads every layer onto the clipboard, and a .PasteSpecial that chooses which layer to keep. Miss the first half and there is nothing to filter — which is exactly the failure mode below.

The rule that matters most: it reads a live clipboard

Here is the mistake behind almost every PasteSpecial method of Range class failed error. PasteSpecial does not remember the range you copied — it reads the Windows clipboard at the instant it runs. If anything clears the clipboard between your .Copy and your .PasteSpecial, the paste has nothing to work with and raises a run-time error:

Range("A1:A20").Copy
MsgBox "About to paste"                 ' this MsgBox can clear the clipboard
Range("C1").PasteSpecial xlPasteValues  ' run-time error 1004: PasteSpecial method ... failed

The clipboard is stateful and fragile. It is wiped by another .Copy or .Cut, by Application.CutCopyMode = False, often by a MsgBox or InputBox, and sometimes just by Excel losing focus. The fix is a discipline, not a trick: keep the .Copy and the .PasteSpecial adjacent, with nothing between them — no dialogs, no other copies, no user interaction. If you need to loop, copy inside the loop right before each paste, never once above it.

The two axes: what crosses, and how it lands

Every PasteSpecial call is described by two independent choices. The first, Paste:=, picks which layer crosses:

Range("A1").PasteSpecial Paste:=xlPasteValues          ' the resulting values only
Range("A1").PasteSpecial Paste:=xlPasteFormulas        ' the formulas (references shift)
Range("A1").PasteSpecial Paste:=xlPasteFormats         ' fonts, fills, borders — no data
Range("A1").PasteSpecial Paste:=xlPasteColumnWidths    ' just the column widths
Range("A1").PasteSpecial Paste:=xlPasteAll             ' everything (same as a plain paste)

The second, Operation:=, decides how the incoming numbers combine with what is already there — xlPasteSpecialOperationAdd, Subtract, Multiply, Divide, or None. Two switches finish the set: Transpose:=True flips rows into columns, and SkipBlanks:=True leaves the target untouched wherever the source cell is empty. The classic "add a correction to every number in a column" trick is one call: put the correction in a cell, .Copy it, then PasteSpecial Paste:=xlPasteValues, Operation:=xlPasteSpecialOperationAdd over the range.

The most common paste — values only — and its format trap

Pasting values only is the reason most people open Paste Special at all: it turns live formulas into the numbers they produced, so you can delete the source or stop volatile formulas recalculating (the full story is in Paste Values). But there is a trap. Bare xlPasteValues copies the number, not the number format, so a column of dates pastes back as serial numbers like 45000 and currency loses its symbol:

srcDates.Copy
dst.PasteSpecial Paste:=xlPasteValues                 ' dates arrive as 45000, 45001, ...
dst.PasteSpecial Paste:=xlPasteValuesAndNumberFormats ' dates arrive as dates — usually what you want

Use xlPasteValuesAndNumberFormats whenever the display matters, which for dates, currency, and percentages is almost always. Reserve bare xlPasteValues for when you genuinely want raw numbers with no formatting carried over.

Cleaning up: CutCopyMode and the marching ants

After a .Copy, Excel puts a shimmering marching-ants border around the source and leaves the clipboard armed. Setting Application.CutCopyMode = False clears both — it removes the border and tells Excel you are done. It is not strictly required for the paste to work, but leaving it on means the next stray Enter key pastes again, and a copied range left "live" is a common source of the fragility above. Make it the last line of any copy/paste block, right after the PasteSpecial.

When PasteSpecial is the wrong tool

PasteSpecial earns its place only when you need a single layer (values, formats, formulas) or an operation (transpose, add). If you are copying a range as-is — value, formula, and format together — you do not need the clipboard at all: src.Copy Destination:=dst does the whole copy in one statement with nothing to clear or corrupt. And if you want values only, dst.Value = src.Value is faster still and never touches the clipboard, sidestepping every failure mode in this guide. The judgment is simple: reach for PasteSpecial for a filtered paste, and for a whole copy prefer Copy Destination. A PasteSpecial where a plain assignment would do is a fragile line waiting to break.

How ExcelMaster helps

The .Copy-then-.PasteSpecial pairing is exactly the kind of thing that works in a quick test and breaks in production — a MsgBox slips between the two lines, a loop copies once above instead of inside, xlPasteValues quietly strips the date formats, and suddenly the macro raises PasteSpecial method of Range class failed or pastes serial numbers to someone who trusts them.

ExcelMaster lets you describe the paste you want — "paste only the values here," "copy just the formatting from that block," "transpose this column into a row" — and it writes the right PasteSpecial with the .Copy kept adjacent, xlPasteValuesAndNumberFormats when the display matters, and Application.CutCopyMode = False to clean up. When a whole copy would do, it skips the clipboard entirely. You keep the workbook and the code.

Frequently asked questions

Why does PasteSpecial method of Range class failed happen?

Because PasteSpecial reads the live clipboard and there is nothing on it. It does not remember the range you copied earlier — it reads whatever is on the clipboard the instant it runs. If a MsgBox, another .Copy, Application.CutCopyMode = False, or Excel losing focus cleared the clipboard between your copy and your paste, the method fails. Keep the .Copy and the .PasteSpecial on adjacent lines with nothing in between.

How do I paste values only in VBA?

.Copy the source, then target.PasteSpecial Paste:=xlPasteValues, and finish with Application.CutCopyMode = False. If the cells are dates, currency, or percentages, use xlPasteValuesAndNumberFormats instead so the formatting comes along — bare xlPasteValues pastes the raw numbers and dates arrive as serials like 45000. For values only with no clipboard at all, target.Value = source.Value is even simpler.

What is the difference between xlPasteValues and xlPasteValuesAndNumberFormats?

xlPasteValues pastes only the resulting numbers and text, dropping the number format, so a date shows as its serial number and currency loses its symbol. xlPasteValuesAndNumberFormats pastes the values and the display format, so dates stay dates and currency keeps its symbol. Use the second whenever the display matters, which is most of the time.

How do I transpose a range with PasteSpecial?

.Copy the source, then paste with Transpose:=True, for example target.PasteSpecial Paste:=xlPasteValues, Transpose:=True. That flips rows into columns and columns into rows. Combine it with xlPasteValues when you only want the data transposed, or xlPasteAll to carry formatting across as well.

Can I use PasteSpecial to add or multiply a range of numbers?

Yes — that is the Operation:= axis. Put the operand in one cell, .Copy it, then over the target range call PasteSpecial Paste:=xlPasteValues, Operation:=xlPasteSpecialOperationAdd (or Multiply, Subtract, Divide). Excel combines the copied number with each existing value in place — a one-call way to bump a whole column by a correction or scale it by a factor.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-09-09.

Related guides: VBA Paste Values · VBA Copy Destination · VBA Copy Paste · VBA Range · VBA Formula