Hello, Tax Automation: Stop Repeating Yourself in Excel

TL;DR: This blog gives you short, tested VBA macros you paste once and run forever. Every post follows the same recipe: a real tax scenario, the full code, and a plain-English walkthrough. You don’t need to learn programming. You need one macro that saves you 20 minutes today.

#This Blog Is For Tax Preparers, Not Developers

You don’t need another tutorial on Range.Find or For Each loops. You need macros that work. Every post on this blog gives you the same thing:

1. A real tax scenario. Not a contrived “suppose you have a list of names” example. A genuine situation from a CPA firm workpaper file — inheriting a broken engagement, prepping for review, archiving a closed year.

2. The full VBA code. Copy it. Paste it into Excel’s VBA editor once. Run it from the Macro dialog forever. No dependencies, no add-ins, no external references.

3. A plain-English walkthrough. Why each design decision was made, what the tricky parts do, and how to adapt it to your firm’s specific workpaper conventions.

4. A sample workbook. Download a real-looking Excel file with deliberate errors or scenarios the macro is designed to catch. Test it. Trust it. Then use it on your own files.

Every macro is tested against realistic tax workpaper data. Every macro restores Excel’s state if it crashes. Every macro tells you what it did with a message box — no silent failures.

#Start With One Macro

The fastest way to get value from this blog: pick one macro that solves a problem you had this week. Paste it in. Use it on a copy of your workpaper. When it works — and it will — use it on the real file.

Give it a week. Then add a second macro. Tax preparers who work with Excel 40+ hours a week typically find 5–8 macros here that become daily tools. But you don’t need all of them today. You need one.

Here are three that solve universal problems:

#Unhide All Sheets

The macro every preparer runs first when they inherit a workbook. Excel has no native “Unhide All” button — you right-click each tab one at a time. This macro loops every sheet and unhides them all in under a second. Ten lines of VBA. Zero configuration. Read the post →

#Find and Replace Across All Sheets

Excel’s native Find & Replace is per-sheet. This macro asks “Find what?” and “Replace with?” once, then executes Range.Replace on every sheet in the workbook. Change "FY2025" to "FY2026" across 30 tabs in one pass. Read the post →

#Auto Table of Contents

Builds a hyperlinked table of contents on a new index sheet with one click. Every sheet name becomes a clickable link. Changes in real-time when tabs are added, renamed, or deleted. The 30-tab workpaper just became navigable. Read the post →

#How the Recipes Work

Every post follows the same structure. Once you’ve read one, you know how to read them all.

The problem statement describes a real tax scenario — the kind of thing that happened to you last week but you didn’t have a name for.

The prerequisites tell you exactly what you need: Excel version, workbook requirements, and any limitations the macro has. If a macro doesn’t handle protected sheets or very-hidden tabs, it says so upfront.

The macro code is the full VBA in a single block. Copy everything between Option Explicit and End Sub. Paste it into a new module. That’s it.

The walkthrough explains the design decisions, not just the syntax. Why a Collection instead of a Dictionary? Why color and a status column? Why restore Application.Calculation in the cleanup? These are the things that make a macro reliable in a tax prep environment.

The Adapt It section shows 3–5 common customizations. Change the target cell from A3 to B2. Skip sheets by name. Adjust the materiality threshold. These are small edits — change a constant, uncomment a line — that make the macro fit your firm’s conventions.

#What You Won’t Find Here

• No tax calculations. These macros do mechanical Excel work — formatting, navigation, validation, and error detection. They don’t compute depreciation recapture or state apportionment factors. Tax law changes; Excel mechanics don’t.

• No firm-specific assumptions. Every macro works on any workbook. No hard-coded sheet names like "Sched-M1" or "TB-Import". If a macro needs to know which sheets to process, it asks you at runtime or auto-detects from the workbook itself.

• No programming prerequisites. You don’t need to know what a Collection is before you use a macro that uses one. The walkthrough explains it. The code works either way.

• No marketing fluff. No “revolutionize your workflow” or “10x your productivity.” Just macros that save you time, with honest descriptions of what they do and what they don’t.

#Prerequisites for Every Macro

• Excel 2016 or later (desktop). The macros use standard VBA that has worked since Excel 2010, but they’re tested on Excel 2016+. Mac Excel and Excel Online don’t support VBA.

• Macros enabled. When you open a workbook with macros, Excel shows a security warning. Click “Enable Content.” For permanent use, add your workpaper folder as a Trusted Location via File → Options → Trust Center.

• The Developer tab visible. Right-click the ribbon → Customize the Ribbon → check “Developer.” This gives you the Visual Basic button and the Macro dialog.

If you’ve never run a macro before: open Excel, press Alt+F11, right-click your workbook in the Project Explorer, choose Insert → Module, paste the code, and press F5 to run it. After that, the macro appears in the Macro dialog (Alt+F8) and you can run it from any sheet.

#Before You Run Any Macro

Three habits that will save you:

1. Save first. VBA has no undo button. If a macro modifies your workbook, save a copy before running it. Once you trust the macro, this becomes less critical — but on the first run, always.

2. Read the message box. Every macro on this blog reports what it did. "12 sheets updated, 3 skipped." If the number doesn’t match your expectation, investigate before closing.

3. Test on a copy. Use the sample workbooks provided with each post to test the macro before running it on a live engagement file. The samples contain deliberate scenarios the macro is designed to handle.

#What’s Coming

This blog ships with a growing library of macros across seven categories:

• Workpaper Management — formatting, navigation, batch operations
• Validation & Checksums — error detection, tie-out verification
• Fixed Assets & Depreciation — §179, bonus, MACRS automation
• Multi-State & Apportionment — state-by-state consistency checks
• Carryforward Schedules — NOL, tax credit, and capital loss tracking
• Tax Season Utilities — estimates, extensions, due diligence checklists
• Getting Started — you’re reading it

New posts are added regularly. Every one follows the same recipe format, the same “no code editor” design philosophy, and the same testing standard.

Start with these: Keep-Alive — the simplest macro on the site, 20 lines of VBA you can paste and run in 60 seconds. Then try Auto Table of Contents — the highest-impact macro for inherited workpapers.

If there’s a macro you wish existed — something you do manually in Excel that feels like it should be automatable — that’s what this blog exists for.