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

TL;DR: This macro scans every sheet in your workbook, checks whether A3 contains a date, and replaces it with whatever period-end you specify. Four lines of logic, forty tabs updated.

#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 changes cells that already contain a date — ignores blanks, text, and numbers
• Only checks cell A3 — change the cell reference in the code if your workpapers use a different cell
• Does not skip hidden or protected sheets — unprotect or unhide those first

#The Macro

Option Explicit

Sub BulkUpdatePeriodEnd()
    ' ── Bulk Date Updater ──────────────────────────────
    ' Checks cell A3 on every worksheet. If it contains a
    ' date, replaces it with the date specified below.
    ' Skips non-date cells (blanks, text, formulas that
    ' don't return a date) without touching them.
    '
    ' Change the constant to your new period-end date.
    ' ────────────────────────────────────────────────────

    Const NEW_DATE As Date = #12/31/2026#

    Dim ws As Worksheet
    Dim changed As Long

    changed = 0

    For Each ws In ThisWorkbook.Worksheets
        If IsDate(ws.Range("A3").Value) Then
            ws.Range("A3").Value = NEW_DATE
            changed = changed + 1
        End If
    Next ws

    If changed > 0 Then
        MsgBox changed & " sheet(s) updated to " _
            & Format(NEW_DATE, "mm/dd/yyyy") & ".", _
            vbInformation, "Done"
    Else
        MsgBox "No date values found in A3 on any sheet.", _
            vbExclamation, "Nothing Updated"
    End If
End Sub

#How It Works

#Why IsDate instead of checking cell format

A cell formatted as Date can still contain text. A cell formatted as General can contain a perfectly valid date. The IsDate function tests the actual value, not the format, so you catch real dates regardless of how the cell is displayed.

If IsDate(ws.Range("A3").Value) Then

#Why a message box at the end

Silent macros are dangerous. You want confirmation of how many sheets were touched — a count of 38 tells you two sheets were skipped (maybe they had a blank or a formula error). That’s information you want before you close the workbook and consider the job done.

#Adapt It