Bulk Date Updater: Change the Period-End Date Across Every Sheet at Once

TL;DR: This macro plants the period-end date on the first sheet, then links every other sheet that already has a date to it with a formula. Cover pages and summaries are left alone. Change one cell, all date-bearing tabs follow.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook where A3 on every sheet (or most sheets) contains a date
• Dates stored as actual Excel dates, not text strings that look like dates

Limitations:

• Only checks cell A3 — change TARGET_CELL in the code if your workpapers use a different cell
• Does not skip hidden or protected sheets — unprotect or unhide those first
• The sheet you’re on when you run the macro becomes the master — navigate there first
• HasDateLike strips common prefixes (FY:, YE:, PE:, etc.) and tests the remainder. If your firm uses an unusual prefix, add it to the prefixes array in the helper function.

#The Macro

Option Explicit

Sub BulkUpdatePeriodEnd()
    ' ── Bulk Date Updater ──────────────────────────────
    ' Makes the current sheet the master: sets its
    ' period-end date, then links every other sheet's
    ' target cell to it — but ONLY on sheets that
    ' already contain a date. The HasDateLike helper
    ' also catches prefixed text like "FY: 12/31/26".
    '
    ' Change the constants below to customize.
    ' ────────────────────────────────────────────────────

    Const NEW_DATE As Date = #12/31/2026#
    Const TARGET_CELL As String = "A3"

    Dim ws As Worksheet
    Dim firstSheetName As String
    Dim changed As Long
    Dim skipped As Long

    changed = 0
    skipped = 0
    firstSheetName = ActiveSheet.Name

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = firstSheetName Then
            ' Master sheet: always gets the date
            ws.Range(TARGET_CELL).Value = NEW_DATE
            ws.Range(TARGET_CELL).NumberFormat = """YE: ""mm/dd/yyyy"
            changed = changed + 1

        ElseIf IsDate(ws.Range(TARGET_CELL).Value) Or _
               HasDateLike(CStr(ws.Range(TARGET_CELL).Value & "")) Then
            ' Date-bearing sheet: link to master
            ws.Range(TARGET_CELL).Formula = "='" & firstSheetName & "'!" & TARGET_CELL
            ws.Range(TARGET_CELL).NumberFormat = """YE: ""mm/dd/yyyy"
            changed = changed + 1

        Else
            ' No date found — skip
            skipped = skipped + 1
        End If
    Next ws

    MsgBox changed & " sheet(s) linked to " _
        & Format(NEW_DATE, "mm/dd/yyyy") & "." & vbCrLf & _
        skipped & " sheet(s) skipped (no date found).", _
        vbInformation, "Done"
End Sub

' ── Helper: strip common prefixes and test what's left ──
Private Function HasDateLike(val As String) As Boolean
    Dim s As String
    s = val

    ' Strip common prefixes (case-insensitive)
    Dim prefixes As Variant
    Dim p As Variant
    prefixes = Array("FY:", "YE:", "PE:", "Q/E:", "QE:", _
                     "As of", "Period End:", "Period-End:", _
                     "Year End:", "Year-End:", "Quarter End:")
    For Each p In prefixes
        If LCase(Left(s, Len(p))) = LCase(p) Then
            s = Trim(Mid(s, Len(p) + 1))
            Exit For
        End If
    Next p

    ' Test what remains
    HasDateLike = IsDate(s)
End Function

#How It Works

#First sheet is the master, rest are mirrors

Whatever sheet you’re on when you run the macro becomes the master — it always gets the actual date value. Every other sheet that already has a date gets a formula like ='TB'!A3 pointing back to it. Change the master once and every linked tab updates instantly, no macro needed.

#Only touches sheets that already have a date

The IsDate check catches proper Excel dates and serial numbers. But what if someone wrote "FY: 12/31/26" as plain text? The HasDateLike helper strips common prefixes — "FY:", "YE:", "PE:", "As of", etc. — and tests what remains with IsDate. No regex, no external objects, works on every Excel install.

ElseIf IsDate(ws.Range(TARGET_CELL).Value) Or _
       HasDateLike(CStr(ws.Range(TARGET_CELL).Value & "")) Then
    ws.Range(TARGET_CELL).Value = NEW_DATE          ' master: real date
Else
    ws.Range(TARGET_CELL).Formula = "='" & firstSheetName & "'!" & TARGET_CELL  ' mirrors: formula link
End If

#The “YE: ” prefix is just a number format

The NumberFormat line wraps the date in a custom format string:

ws.Range(TARGET_CELL).NumberFormat = """YE: ""mm/dd/yyyy"

This displays 12/31/2026 as YE: 12/31/2026 in the cell, but the underlying value is still a proper Excel date (serial number). Formulas that reference A3 for date math — EOMONTH, DAYS, fiscal-period lookups — all work without needing to strip the prefix. It’s pure presentation.

#Why a message box at the end

Silent macros are dangerous. The message box tells you exactly what happened — 7 sheet(s) linked, 3 skipped — so you can spot-check before moving on. If you expected 10 links and got 7, something’s off.

#Before you run: save first (or work on a copy)

VBA has no undo button. Once this macro writes to every sheet, there’s no Ctrl+Z. The standard approach in tax shops:

1. Save the workbook right before running — one click, total safety net
2. Work on a copy if you’re testing — File → Save As a duplicate first
3. Close other workbooks so ActiveSheet doesn’t accidentally target the wrong file
4. Check the message box count matches your expectations — if you have 8 workpaper tabs and the message says 6 linked, 2 skipped, investigate the two skipped ones before you close it

Some developers build an undo system by storing old values in a dictionary before making changes, but for a macro this small, a save-then-run habit is simpler and just as safe.

#Adapt It