Paste Values Everywhere: Freeze Every Formula in the Workbook for Archiving

TL;DR: This macro replaces every formula on every visible sheet with its current value. Double-confirm gate because there’s no undo. Built for the moment the engagement is done and you need to send a clean, formula-free workbook to the client or archive a permanent snapshot for the file room.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook with formulas you want to permanently replace with values
• A backup of the file. This macro is irreversible. Run Timestamped Backup first, or make a manual copy

What the macro does:

• Scans every visible sheet for formula cells
• Replaces each formula with its current calculated value using rng.Value = rng.Value
• Reports the total number of formulas converted and which sheets were affected

Limitations:

• No undo. Formulas are permanently replaced with values. There is no Ctrl+Z, no version history, no recovery — unless you saved a backup first
• Only processes visible sheets — hidden sheets are skipped. If you have calculation chains feeding through hidden sheets, unhide them first
• Does NOT touch hard-coded numbers, text, or blank cells — only formulas
• If a formula currently evaluates to an error (#REF!, #DIV/0!, #N/A), the error value becomes permanent — it won’t self-heal when you fix upstream cells
• Array formulas (CSE) and dynamic array formulas are converted to their current spilled values — the dynamic behavior is lost
• Does not remove named ranges, data validation, conditional formatting, or other metadata — only cell formulas

#The Macro

Option Explicit

Sub PasteValuesEverywhere()
    ' ── Paste Values Everywhere ───────────────────────
    ' Converts every formula on every visible sheet to
    ' its current calculated value.
    '
    ' WARNING: Irreversible. Run TimestampedBackup
    ' first. There is no undo.
    '
    ' Double-confirm gate to prevent accidental
    ' execution. Reports count of formulas converted
    ' and affected sheet names.
    ' ────────────────────────────────────────────────────

    ' ── State management ───────────────────────────────
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False

    ' ── Variables ──────────────────────────────────────
    Dim ws As Worksheet
    Dim formulaRange As Range
    Dim startWS As Worksheet
    Dim totalFormulas As Long
    Dim affectedSheets As String
    Dim sheetCount As Long
    Dim formulaCount As Long

    ' ── Error handling ─────────────────────────────────
    On Error GoTo CleanUp

    Set startWS = ActiveSheet
    totalFormulas = 0
    sheetCount = 0
    affectedSheets = ""

    ' ── Stage 1 confirm: general warning ───────────────
    If MsgBox("WARNING: This will replace ALL formulas " & _
              "with their current values on EVERY visible " & _
              "sheet." & vbCrLf & vbCrLf & _
              "This is PERMANENT and cannot be undone " & _
              "with Ctrl+Z." & vbCrLf & vbCrLf & _
              "TIP: Run Timestamped Backup first." & _
              vbCrLf & vbCrLf & _
              "Continue?", _
              vbYesNo + vbExclamation + vbDefaultButton2, _
              "Paste Values Everywhere — Stage 1 of 2") = vbNo Then
        Exit Sub
    End If

    ' ── Stage 2 confirm: final gate ────────────────────
    If MsgBox("FINAL WARNING: Are you ABSOLUTELY sure?" & _
              vbCrLf & vbCrLf & _
              "Every formula on every visible sheet will " & _
              "become a permanent value.", _
              vbYesNo + vbCritical + vbDefaultButton2, _
              "Paste Values Everywhere — FINAL CONFIRMATION") = vbNo Then
        Exit Sub
    End If

    ' ── Convert formulas to values ─────────────────────
    For Each ws In ThisWorkbook.Worksheets
        If ws.Visible <> xlSheetVisible Then GoTo NextSheet

        On Error Resume Next
        Set formulaRange = ws.Cells.SpecialCells(xlCellTypeFormulas)
        On Error GoTo CleanUp

        If Not formulaRange Is Nothing Then
            formulaCount = formulaRange.Cells.Count
            formulaRange.Value = formulaRange.Value
            totalFormulas = totalFormulas + formulaCount
            sheetCount = sheetCount + 1
            affectedSheets = affectedSheets & "  • " & ws.Name & _
                             " (" & formulaCount & ")" & vbCrLf
            Set formulaRange = Nothing
        End If

NextSheet:
    Next ws

    ' ── Report results ─────────────────────────────────
    If totalFormulas = 0 Then
        MsgBox "No formulas found on any visible sheet.", _
               vbInformation, "Paste Values Everywhere"
    Else
        MsgBox "Converted " & Format(totalFormulas, "#,##0") & _
               " formula(s) to values across " & sheetCount & _
               " sheet(s):" & vbCrLf & vbCrLf & _
               affectedSheets, _
               vbInformation, "Paste Values Everywhere — Done"
    End If

CleanUp:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True

    If Not startWS Is Nothing Then
        On Error Resume Next
        startWS.Activate
        On Error GoTo 0
    End If

    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description & _
               vbCrLf & vbCrLf & _
               "The workbook is in an unknown state. DO NOT save " & _
               "until you verify the data is intact.", _
               vbCritical, "Macro Error"
    End If
End Sub

#How It Works

#The double-confirm gate: why it’s necessary

This is the most destructive macro on the site. It replaces every formula in the workbook with a static value. There’s no undo, no Ctrl+Z, no “revert to saved” unless you saved a backup. The double-confirm MsgBox sequence isn’t annoying — it’s the safety net.

' Stage 1: general warning with tip to back up
If MsgBox("WARNING: This will replace ALL formulas..." & _
          vbCrLf & vbCrLf & _
          "TIP: Run Timestamped Backup first." & ...
          vbYesNo + vbExclamation + vbDefaultButton2, ...) = vbNo Then
    Exit Sub
End If

' Stage 2: final gate with different button style
If MsgBox("FINAL WARNING: Are you ABSOLUTELY sure?" & ...
          vbYesNo + vbCritical + vbDefaultButton2, ...) = vbNo Then
    Exit Sub
End If

Stage 1 uses vbExclamation (yellow triangle) and reminds the user to back up. Stage 2 uses vbCritical (red X) and shorter, more urgent text. Both stages default the focus to the No button (vbDefaultButton2) so an accidental Enter press cancels rather than proceeds.

#SpecialCells(xlCellTypeFormulas) — finding every formula at once

The macro uses Excel’s built-in SpecialCells method to locate formula cells in one operation, rather than looping through every cell on the sheet:

On Error Resume Next
Set formulaRange = ws.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo CleanUp

xlCellTypeFormulas returns a Range containing every cell with a formula — SUM, VLOOKUP, IF, INDIRECT, array formulas, everything. A sheet with 10,000 cells and 200 formulas returns a 200-cell range, not 10,000.

The On Error Resume Next/On Error GoTo CleanUp sandwich handles sheets with no formulas. SpecialCells raises an error if no cells match the criteria — without the resume, the macro would crash on every data-only sheet. With it, formulaRange is Nothing and the sheet is skipped.

#rng.Value = rng.Value — the one-line conversion

formulaRange.Value = formulaRange.Value

This line does all the work. When you read .Value from a formula cell, VBA returns the evaluated result (the number, text, or error the formula displays). When you write that same value back to .Value, VBA stores it as a constant. The formula is gone; the value remains.

This is a single-line equivalent of Copy → Paste Special → Values, but it works on the entire SpecialCells range in one assignment. No clipboard involved, no Application.CutCopyMode = False needed.

#Why events and calculation are disabled

Three application-level flags are toggled for this macro:

Application.ScreenUpdating = False      ' No flicker
Application.Calculation = xlCalculationManual  ' No recalc cascade
Application.EnableEvents = False        ' No event handlers firing

ScreenUpdating is standard — avoids visual flicker as each sheet processes.

Calculation is essential. Without it, Excel recalculates every formula on the sheet after each range is converted. With 30 sheets of interdependent formulas, this could trigger a cascade of partial recalculations that slows the macro to a crawl. xlCalculationManual freezes calculation until we’re done, then xlCalculationAutomatic in the cleanup restores it.

EnableEvents is the one most people forget. If any sheet has a Worksheet_Change event handler — auto-formatting, logging, recalculation triggers — converting formulas to values fires that event for every cell. For 2,800 cells, that’s 2,800 event handler invocations. EnableEvents = False suppresses them all.

#The per-sheet breakdown in the results MsgBox

The final message box doesn’t just say “converted 2,847 formulas.” It lists every affected sheet with its count:

Converted 2,847 formula(s) to values across 22 sheet(s):

  • TB (156)
  • Sched-A Income (89)
  • Sched-E Deductions (112)
  • Fixed Assets (204)
  • Depreciation (178)
  ...

This is your audit trail. If you expected “Sched-M1 Rec” to appear but it doesn’t, you know that sheet had no formulas — or it was hidden. If a sheet you didn’t expect appears, you know to check it before sending. The counts let you spot-check: if Fixed Assets usually has 204 formulas and now shows 12, something changed between when you built the sheet and when you archived it.

#Why hidden sheets are skipped

The ws.Visible <> xlSheetVisible check skips hidden and very-hidden sheets. This is intentional — if a sheet is hidden, you probably don’t want formulas there converted to values anyway. Hidden sheets often contain lookup tables, validation lists, or calculation engines that formulas on visible sheets reference. Converting those formulas would break the visible sheets during the macro execution, producing #REF! values that then get frozen forever.

If you DO want hidden sheets processed, change the check to remove the skip or use xlSheetVeryHidden to skip only very-hidden sheets.

#The “do not save” error message

The error handler includes a special message if something goes wrong mid- conversion:

MsgBox "Error " & Err.Number & ": " & Err.Description & _
       vbCrLf & vbCrLf & _
       "The workbook is in an unknown state. DO NOT save " & _
       "until you verify the data is intact.", _
       vbCritical, "Macro Error"

If the macro crashes partway through — say, a protected sheet that can’t be written to — some sheets will be converted and some won’t. Saving in this state permanently locks in the partial conversion. The error message tells the user to close without saving and reopen from the backup they definitely made (right?).

#Adapt It