VBA Copy Paste in Excel — Skip the Clipboard for Faster, Unbreakable Macros

Tested on: Excel 365 v2509 · Excel 2021 · Excel 2019 · last verified 2026-06-12

TL;DR — The macro recorder writes Select → Copy → Paste: three steps that route your data through the Windows clipboard. That's slow, it can be wiped by anything else running, and it leaves the screen flashing. If you only need the values, the whole thing collapses to one line that never touches the clipboard: rngB.Value = rngA.Value.

Sub CopyDemo()
    ' (1) Values only — no clipboard, instant, can't be interrupted
    Sheet2.Range("A1:C100").Value = Sheet1.Range("A1:C100").Value

    ' (2) Values AND formatting in one step — uses .Copy with a Destination
    Sheet1.Range("A1:C100").Copy Destination:=Sheet2.Range("A1")

    ' (3) Just one aspect (values / formats / formulas) — needs the clipboard
    Sheet1.Range("A1:C100").Copy
    Sheet2.Range("A1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False              ' clear the clipboard "marching ants"
End Sub

The mental model: the clipboard is a middleman your code doesn't need

When you copy and paste, the clipboard is a holding area — you copy here, click there, paste. The macro recorder faithfully records those human steps, so recorded code is full of .Select, Selection.Copy, and ActiveSheet.Paste. But your code already holds direct references to both ranges. It doesn't need to click anything, and it doesn't need a holding area — it can move the data straight across.

That single shift explains everything below. Copy-Paste isn't wrong; it's a translation of a manual workflow into code, and most of the time the manual workflow had a step your code can simply skip. Ask "am I moving values, or values-and-formatting, or one specific aspect?" and the right tool falls out — and two of the three answers never go near the clipboard.

The one rule: assignment moves values; the clipboard is only for aspects

Here's the rule that replaces 90% of recorded Copy-Paste:

To move values, assign one range's .Value to another's — no Copy, no Paste, no clipboard. You only need Copy + PasteSpecial when you want one specific aspect of a cell (values only, formats only, formulas only) and nothing else.

.Value = .Value is direct, instant, and — crucially — uninterruptible. Nothing can wipe it mid-operation because it never uses shared global state. Compare the two ways to "paste values":

' ⚠ RECORDER STYLE — clipboard, three steps, fragile
Range("A1:A100").Select
Selection.Copy
Range("C1").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

' ✓ DIRECT — one line, no clipboard, nothing to clear or interrupt
Range("C1:C100").Value = Range("A1:A100").Value

The direct form is faster, shorter, and survives anything happening on the machine while it runs. The recorded form does the same job by way of a global resource three other programs might be using.

The trap: PasteSpecial depends on a clipboard that anything can wipe

When you do need PasteSpecial — say you want to keep a cell's formatting but drop its formulas — remember that Copy puts data on the system clipboard, and the clipboard is global, volatile state. Two things bite people:

Range("A1:A100").Copy
MsgBox "About to paste"          ' ⚠ some dialogs / recalcs can clear the clipboard...
Range("C1").PasteSpecial xlPasteValues   ' ...and now this fails or pastes nothing

The fixes are habits, not tricks:

• Don't put anything between Copy and PasteSpecial — no MsgBox, no calculation, no second copy. Copy, paste, done.
• Always clear with Application.CutCopyMode = False afterwards, so you don't leave the source cells outlined with "marching ants" and a primed clipboard.
• If you only want values, don't reach for PasteSpecial at all — use .Value = .Value and the whole class of clipboard problems disappears.

For copying values and formatting together, rng.Copy Destination:=target is the sweet spot: it's a single statement, it carries everything, and the Destination form does the move without leaving the clipboard primed.

When to use which

The opinion: never record Copy-Paste and ship it

The line I'll defend: recorded Copy-Paste is a draft, not a destination. The recorder is a great way to discover which object and method you need, but the code it writes — Select, Selection, ActiveSheet.Paste, no CutCopyMode cleanup — is the slowest, most fragile way to do the job. It depends on what's selected, it flickers the screen, and it routes everything through a clipboard that any other app can stomp on.

Strip the .Selects, decide what you're actually moving, and the answer is almost always one line. If it's just values, .Value = .Value. If it's everything, .Copy Destination:=. Save PasteSpecial for the genuine "one aspect only" case, keep it sandwiched tightly between Copy and the paste, and clear CutCopyMode after. A macro written that way runs in a fraction of the time and can't be broken by a stray dialog — which is the whole point of automating it.

Common VBA Copy-Paste mistakes (and the fix)

When the copying piles up — describe the move instead

You didn't set out to learn the clipboard's failure modes. You wanted "pull the paid rows from this sheet into the summary, values only, every morning." By the time you've de-recorded the .Selects, decided between .Value and PasteSpecial, and remembered to clear CutCopyMode, the copy plumbing is the macro. ExcelMaster Agent lets you describe the move in plain English — "copy the rows where status is paid into the Summary sheet as values, keeping the header" — and it writes Python that moves the data directly, no clipboard involved, backing up your workbook first. Try it free →

Related guides

• VBA Range — Reference Cells Without Selecting Them
• VBA Array — Read and Write a Range in One Hit
• VBA VLookup — The "Not Found" Crash & When a Dictionary Wins
• VBA Outlook — Send Email from a Macro the Right Way
• VBA For Loop in Excel — 8 Real-World Examples

FAQ

How do I copy and paste in VBA without the clipboard? For values, assign directly: Range("C1:C100").Value = Range("A1:A100").Value. To carry values and formatting in one step without leaving the clipboard primed, use Range("A1:A100").Copy Destination:=Range("C1"). Both avoid the slow, interruptible Copy → PasteSpecial round-trip.

How do I paste values only in VBA? The cleanest way is targetRange.Value = sourceRange.Value, which copies values with no clipboard at all. If you're already mid-Copy, use Range("C1").PasteSpecial Paste:=xlPasteValues and then Application.CutCopyMode = False. Don't place a MsgBox or calculation between the copy and the paste, or the clipboard can be cleared first.

What does Application.CutCopyMode = False do? It clears the clipboard's "marching ants" and the pending copy after you've pasted. Setting it prevents stale clipboard state and the source range staying visibly outlined. Run it right after a Copy + PasteSpecial sequence.

Why is my VBA copy-paste so slow? Usually because it .Selects ranges and routes data through the clipboard one block at a time, often inside a loop. Move whole ranges at once with .Value = .Value, drop the .Select/.Activate calls, and set Application.ScreenUpdating = False while it runs.

What's the difference between Copy Destination and PasteSpecial? rng.Copy Destination:=target copies everything (values, formats, formulas) in one statement without leaving the clipboard primed. PasteSpecial exists to copy one aspect only — values, formats, or formulas — and it requires a prior Copy plus a CutCopyMode = False cleanup. Use Destination for a full copy, PasteSpecial when you want just one part.