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

VBA Paste Values in Excel — Freeze Formulas Into Their Results the Right Way

|

VBA Paste Values in Excel — Freeze Formulas Into Their Results the Right Way

TL;DR — Pasting values replaces a live formula with the number it currently shows. That number then stands on its own: you can delete the cells it referenced, the workbook stops recalculating it, and external links go quiet. The clipboard recipe is .Copy then PasteSpecial Paste:=xlPasteValues — but bare xlPasteValues drops the number format, so dates come back as serials like 45000. Use xlPasteValuesAndNumberFormats when the display matters, and for a plain freeze with no clipboard at all, dst.Value = src.Value.

' Freeze a range of formulas into their results, keeping the display:
src.Copy
src.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False

' The no-clipboard equivalent for values only (loses number formats):
dst.Value = src.Value

A formula is a promise to recalculate: =Sheet2!A1*1.1 is not a number, it is an instruction that re-runs whenever Sheet2!A1 changes. Most of the time that is exactly what you want. But sometimes you need the answer to stop being a promise and become a fact — a month you are archiving, a snapshot you are emailing, a volatile formula you want to stop churning. That is what pasting values does. It is the most-searched corner of PasteSpecial, and it has one trap worth knowing before you use it.

What you'll learn

  • The mental model — pasting values turns a promise to recalculate into a plain number
  • The one rule that saves data — freeze before you delete the source, not after
  • The format trap — xlPasteValues strips the number format and dates become serials
  • The three real jobs — archiving a result, stopping volatiles, breaking external links
  • The no-clipboard shortcut — dst.Value = src.Value
  • When not to freeze, because a formula is doing its job

A formula links a cell to its inputs. Pasting values cuts that link and leaves behind the number that was showing at the moment you did it — a snapshot. The pixels look identical; the behavior is completely different. Before, the cell was =A1+A2 and would follow A1 and A2 forever. After, the cell is the literal 350, and A1 and A2 can change, move, or vanish without it noticing.

That single distinction — link versus snapshot — is the whole decision. If you ask "does this number still need to track its inputs?" the answer tells you whether to leave the formula or freeze it. Pasting values is you answering "no, this result is final now."

The rule that matters most: freeze before you delete the source

Here is the mistake that destroys data. You build a report with formulas that reference a raw-data sheet, then delete the raw-data sheet to slim the file — and every formula collapses to #REF!, because the cells they pointed at are gone:

dst.Formula = "=RawData!A1 * 1.1"   ' a live link to another sheet
Worksheets("RawData").Delete         ' the source disappears...
' dst now shows #REF! — the number is lost

The fix is order: freeze the results into values first, then delete the source. Once dst.PasteSpecial Paste:=xlPasteValues has run, the cell holds a real number that owes nothing to RawData, so deleting the sheet is safe. The rule generalizes: any time you are about to remove, overwrite, or move the cells a formula depends on, paste values first. A #REF! cannot be undone once the file is saved and closed.

The format trap: values only strips the number format

This is the surprise that sends people back to search. xlPasteValues copies the value — and a value in Excel is a bare number. A date is stored as a serial number (days since 1900), currency as a plain number, a percentage as a decimal. The formula's number format is what showed them as 2026-03-15, $1,200, and 35%. Paste values only and that formatting is left behind:

src.Copy
src.PasteSpecial Paste:=xlPasteValues                 ' 2026-03-15 becomes 46091
src.PasteSpecial Paste:=xlPasteValuesAndNumberFormats ' 2026-03-15 stays 2026-03-15

So the honest default for anything a human reads is xlPasteValuesAndNumberFormats, which carries the display format across with the number. Reach for bare xlPasteValues only when you specifically want the raw underlying numbers — feeding a calculation, exporting to a system that reformats anyway.

Three jobs paste-values really does

Freezing formulas is not one task but three, and naming them tells you when to do it:

  • Archive a result. A monthly summary computed from this month's data should not change when next month's data lands. Freeze it and the number is the month's, permanently.
  • Stop volatile formulas. NOW(), RAND(), TODAY(), and OFFSET() recalculate on every edit, which is noise once you have the value you wanted. Pasting values pins them.
  • Break external links. A workbook full of =[Budget.xlsx]Sheet1!A1 nags every recipient with an update prompt. Freeze those cells and the file carries its numbers with no links to chase.

All three are the same operation with different intent, and all three are unsafe to do by deleting the source first — freeze, then remove.

The no-clipboard shortcut: dst.Value = src.Value

If all you want is values — no formats, no operation, no transpose — you do not need the clipboard at all. Assigning one range's .Value to another writes the numbers straight across:

dst.Value = src.Value       ' values only, instant, never touches the clipboard

This is the fastest way to freeze in place — rng.Value = rng.Value overwrites a range's formulas with their own results in one line — and it sidesteps every clipboard failure mode from PasteSpecial. Its one limit is that it moves only values: no number formats, so use PasteSpecial Paste:=xlPasteValuesAndNumberFormats when the display has to survive, and see Copy Destination for the full picture of copying without the clipboard.

When not to freeze

Freezing is destructive — you are throwing away a formula. Do not do it to a cell whose number should keep tracking its inputs: a running total, a linked summary, a model whose whole point is to recompute when assumptions change. If you freeze those, the next reader edits an input and the "total" silently stops moving, which is worse than any recalculation lag. Freeze a result that is final; leave a formula that is still working. When in doubt, freeze a copy on a snapshot sheet and keep the live formulas where they are.

How ExcelMaster helps

Paste-values is a one-line idea with two ways to lose data: freeze after deleting the source and you get #REF!; use bare xlPasteValues on dates and you email someone a column of 45000s. Both compile, both run, and both look fine until it is too late to fix.

ExcelMaster lets you say what you mean — "freeze this month's numbers," "convert these formulas to values but keep the dates," "break the links before I send it" — and it orders the steps safely (freeze first, delete second), picks xlPasteValuesAndNumberFormats when a human will read the result, and uses dst.Value = src.Value when the clipboard is not needed. You keep the workbook and the code.

Frequently asked questions

How do I convert formulas to values in VBA?

Freeze them in place: rng.Value = rng.Value overwrites each formula with the number it currently produces, in one line and with no clipboard. If you need to keep the display format on dates or currency, use the clipboard form instead — rng.Copy then rng.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, followed by Application.CutCopyMode = False.

Why did my dates turn into numbers after pasting values?

Because xlPasteValues pastes the value without the number format, and a date's underlying value is a serial number like 45000. The format was what displayed it as a date. Use xlPasteValuesAndNumberFormats so the display format comes across too, and the dates stay dates.

How do I stop a formula recalculating without deleting it?

Replace it with its result — paste values. Copy the cells and PasteSpecial Paste:=xlPasteValuesAndNumberFormats, or use rng.Value = rng.Value. This is the usual fix for volatile formulas like NOW(), RAND(), and OFFSET() that keep changing on every edit: once frozen, the number is fixed.

Will pasting values break my formulas if I delete the source sheet?

It prevents that break — as long as you freeze first. If a formula references another sheet and you delete that sheet, the formula becomes #REF! and the number is lost. Paste values before deleting, so the cell holds a real number that no longer depends on the source.

What is the difference between .Value and .Value2 when freezing?

.Value returns dates and currency as their formatted types, while .Value2 returns the raw underlying number (a date as its serial). For a plain freeze, rng.Value = rng.Value is fine; use .Value2 only when you deliberately want the raw serials and no currency or date typing — see VBA Value2 for the distinction.

Tested in

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

Related guides: VBA PasteSpecial · VBA Copy Destination · VBA Copy Paste · VBA Formula · VBA Value2