Find and Replace — All Sheets: Change 'FY2025' to 'FY2026' Across 30 Tabs in One Pass

TL;DR: Excel’s native Find & Replace is per-sheet. This macro asks “Find what?” and “Replace with?” once, then updates every sheet in the workbook and tells you how many replacements were made on each one. Change "FY2025" to "FY2026" across 30 tabs before the partner finishes reading your email.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook with text you want to find and replace across multiple sheets
• No special sheet names, no setup — the macro prompts you at runtime

Limitations:

• Replaces in cell values only — formula text (like ="FY2025 Total") is not modified. If your text appears inside formulas, consider editing those separately (they’re less common in tax workpapers)
• Case-insensitive partial matching — searching for "2025" will match "FY2025", "2025-001", and "2025 Review"
• The replacement cannot be undone with Ctrl+Z — save a copy before running
• Works on ThisWorkbook — the workbook containing the macro. Store in Personal Macro Workbook to run on any open file

#The Macro

Option Explicit

Sub FindAndReplaceBulk()
    ' ── Find and Replace — All Sheets ──────────────────
    ' Prompts for Find and Replace text, then runs
    ' Find & Replace on every worksheet. Shows a confirm
    ' dialog before executing and reports per-sheet
    ' replacement counts in the result message.
    '
    ' Case-insensitive, partial match. Replaces in
    ' cell values only (not inside formulas).
    ' ────────────────────────────────────────────────────

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

    ' ── Variables ──────────────────────────────────────
    Dim findText As String, replaceText As String
    Dim ws As Worksheet
    Dim cell As Range, firstAddr As String
    Dim found As Long
    Dim totalReplaced As Long, sheetsAffected As Long

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

    ' ── Get user input ──────────────────────────────────
    findText = InputBox("Find what?", "Find and Replace — All Sheets")
    If findText = "" Then GoTo CleanUp

    replaceText = InputBox("Replace with?", "Find and Replace — All Sheets")

    ' ── Confirm before proceeding ──────────────────────
    Dim confirm As VbMsgBoxResult
    confirm = MsgBox("Replace all '" & findText & "' with '" & _
                     replaceText & "' across ALL sheets?", _
                     vbYesNo + vbQuestion, "Confirm Replace All")
    If confirm <> vbYes Then GoTo CleanUp

    ' ── Process each sheet ─────────────────────────────
    totalReplaced = 0
    sheetsAffected = 0

    For Each ws In ThisWorkbook.Worksheets
        found = 0

        Set cell = ws.Cells.Find(What:=findText, _
                                 LookIn:=xlValues, _
                                 LookAt:=xlPart, _
                                 MatchCase:=False)

        If Not cell Is Nothing Then
            firstAddr = cell.Address
            Do
                cell.Value = Replace(cell.Value, findText, _
                                     replaceText, , , vbTextCompare)
                found = found + 1
                Set cell = ws.Cells.FindNext(cell)
            Loop While Not cell Is Nothing And _
                        cell.Address <> firstAddr
        End If

        If found > 0 Then
            sheetsAffected = sheetsAffected + 1
            totalReplaced = totalReplaced + found
        End If
    Next ws

    ' ── Report results ──────────────────────────────────
    If totalReplaced = 0 Then
        MsgBox "No occurrences of '" & findText & _
               "' found in any sheet.", _
               vbInformation, "Find and Replace"
    Else
        MsgBox "Replaced " & totalReplaced & " occurrence(s) " & _
               "across " & sheetsAffected & " sheet(s).", _
               vbInformation, "Find and Replace"
    End If

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

    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
               vbCritical, "Macro Error"
    End If
End Sub

#How It Works

#Two InputBoxes, one decision

The macro asks two questions and one confirmation — that’s the entire user interface. No code editor, no constants to change, no sheet names to type. The user types the old text, types the new text, and clicks Yes to run.

findText = InputBox("Find what?", "Find and Replace — All Sheets")
If findText = "" Then GoTo CleanUp

If the user clicks Cancel or leaves the Find box empty, the macro exits cleanly. No changes are made. This is important — a macro that starts replacing text with no Find string would corrupt every cell it touches.

The second InputBox for the replacement text allows an empty string. Typing nothing and clicking OK means “delete every occurrence of the Find text.” This is a deliberate design choice — you might want to strip "DRAFT " from every header without replacing it with anything.

#The confirm dialog is the safety net

confirm = MsgBox("Replace all '" & findText & "' with '" & _
                 replaceText & "' across ALL sheets?", _
                 vbYesNo + vbQuestion, "Confirm Replace All")
If confirm <> vbYes Then GoTo CleanUp

VBA has no undo button. If you accidentally search for "a" and replace with "b", every a in every cell on every sheet becomes b — and there’s no Ctrl+Z. The confirm dialog shows you exactly what you’re about to do and gives you one last chance to abort.

The + vbQuestion adds the question-mark icon, which signals “this needs your attention” more clearly than the default information icon. The vbYesNo gives two buttons so there’s no ambiguity: click No and nothing happens.

#Find/FindNext loop with count

The macro uses a Find/FindNext loop instead of Range.Replace for one reason: Excel’s Range.Replace returns only True or False — it won’t tell you how many cells were changed. A silent replace-all is dangerous in a tax workpaper.

Set cell = ws.Cells.Find(What:=findText, LookIn:=xlValues, ...)
If Not cell Is Nothing Then
    firstAddr = cell.Address
    Do
        cell.Value = Replace(cell.Value, findText, _
                             replaceText, , , vbTextCompare)
        found = found + 1
        Set cell = ws.Cells.FindNext(cell)
    Loop While Not cell Is Nothing And _
                cell.Address <> firstAddr
End If

The Find method starts at the first match. FindNext moves to the next match. The loop exits when FindNext wraps around and returns to the address of the first match. Each iteration replaces the cell value and increments the counter.

The vbTextCompare argument on Replace makes it case-insensitive — searching for "fy2025" matches "FY2025", "Fy2025", and "fy2025". This matches the behavior of Excel’s native Find & Replace.

#Values only, not formulas

LookIn:=xlValues

The Find targets cell values — the number or text the cell displays. It skips formula text. If cell B14 contains ="Q4 "&FY2025&" Close", searching for "FY2025" won’t find it because the displayed value is "Q4 FY2025 Close" and the formula text FY2025 is not a cell value.

Why values-only? In tax workpapers, the year string almost always appears in typed headers, notes, and labels — not inside formulas. Changing formula text is riskier (a typo can break the formula) and rarer. If you need formula-level replacement, see the Adapt It section.

#The message box is your audit

MsgBox "Replaced " & totalReplaced & " occurrence(s) " & _
       "across " & sheetsAffected & " sheet(s).", _
       vbInformation, "Find and Replace"

Saving before running tells you the macro didn’t crash. The message box tells you it actually did something. If you expected 30 replacements and got 12, something’s wrong — maybe half your sheets use "FY 2025" (with a space) instead of "FY2025". The message box gives you the count to do a quick mental spot-check.

#Adapt It