Wauvel

Excel like a finance pro.

The functions, VBA, and shortcuts a finance operator actually uses — curated, with CFO examples. Prefer them pre-built? Grab a ready-made model.

← All VBA snippets

Turn formulas into their values

Freeze a range so its formulas collapse to the numbers they returned — no clipboard needed.

1What it does

Sets a range equal to its own value (.Value = .Value), which replaces every formula with the result it currently shows. Perfect right before you send a file, break a link, or archive a month so the numbers can't shift underneath you. The commented clipboard version (Copy then PasteSpecial xlPasteValues) is the one to use when you're moving values from one place to another.

2The code

Sub PasteValues()
    ' The whole trick: set a range equal to its own value and the formulas
    ' collapse to the numbers they returned. No clipboard needed.
    With ActiveSheet.Range("B2:B100")
        .Value = .Value
    End With

    ' The clipboard version — use this when you're copying FROM one place TO
    ' another and only want the values to land:
    '   Range("B2:B100").Copy
    '   Range("E2").PasteSpecial Paste:=xlPasteValues
    '   Application.CutCopyMode = False   ' clears the marching ants
End Sub

Paste into the Visual Basic Editor (Alt + F11 → Insert → Module), then run and save as macro-enabled (.xlsm).

New to macros? Set up in 5 minutes▾
  1. 1

    Don't see the Developer tab in the ribbon?

    You don't strictly need it — Alt + F11 opens the editor directly — but it makes running macros easier.
    • Windows: File → Options → Customize Ribbon → tick Developer in the right-hand list → OK.
    • Mac:Excel → Preferences → Ribbon & Toolbar → tick Developer → Save.
  2. 2

    Paste in the code

    Press Alt + F11 to open the Visual Basic editor, then Insert → Moduleand paste the snippet's code into the blank window. Close it with Alt + Q.
  3. 3

    Run it

    Press Alt + F8, pick the macro's name, and click Run — that's it. (Pasted a custom function instead? Just type it into a cell like any built-in: =GrossMargin(B2, B3).)
  4. 4

    Keep the macro — save as .xlsm

    File → Save As → Excel Macro-Enabled Workbook (.xlsm). A plain .xlsx silently drops the code when you save.
  5. 5

    Macros blocked?

    Click Enable Content on the yellow bar. If you downloaded the file, you may first need to right-click it → Properties → tick Unblock → OK, then reopen.

Heads up: macros can't be undone with Ctrl + Z — save a copy before running one that changes your workbook.

3When you use it

  • Lock down a month-end snapshot so recalculation can't change it.
  • Break links to other workbooks before sharing a file.
  • Convert volatile formulas (TODAY, RAND, OFFSET) to static values.

4See it in action

A simulation — press Run to perform what the macro does to a sample workbook.

CellContents
B2=SUM(C2:C9)
B3=B2*0.3
B4=TODAY()

These cells hold formulas.

5Pitfalls

You meant to copy but overwrote the formulas in place.

Fix: The .Value = .Value form is in-place and can't be undone with Ctrl+Z from a macro — save first, or copy to a different range.

PasteSpecial leaves the marching ants and a stuck clipboard.

Fix: Add Application.CutCopyMode = False right after the paste.

6No-code alternatives

  • Paste Values by hand — Ctrl+C then Ctrl+Alt+V, V, Enter does it for a one-off — see the paste-values tip.
  • Range = Range — Assign values sheet-to-sheet directly: dst.Value = src.Value, no clipboard involved.

Rather not write macros? Wauvel's free tools generate branded, formula-driven Excel for you — no VBA required.

Learn the moves here — or let Wauvel run them on your numbers.

Get my free report →