VBA snippets
Each macro has its own page — what it does, the code, a before/after simulation, pitfalls, and a no-code alternative.
New to macros? Set up in 5 minutes▾
- 1
Don't see the Developer tab in the ribbon?
You don't strictly need it — Alt + F11 opens the editor directly — but it makes running macros easier.- Windows: File → Options → Customize Ribbon → tick Developer in the right-hand list → OK.
- Mac: Excel → Preferences → Ribbon & Toolbar → tick Developer → Save.
- 2
Paste in the code
Press Alt + F11 to open the Visual Basic editor, then Insert → Module and paste the snippet's code into the blank window. Close it with Alt + Q. - 3
Run it
Press Alt + F8, pick the macro's name, and click Run — that's it. (Pasted a custom function instead? Just type it into a cell like any built-in:=GrossMargin(B2, B3).) - 4
Keep the macro — save as .xlsm
File → Save As → Excel Macro-Enabled Workbook (.xlsm). A plain .xlsx silently drops the code when you save. - 5
Macros blocked?
Click Enable Content on the yellow bar. If you downloaded the file, you may first need to right-click it → Properties → tick Unblock → OK, then reopen.
Heads up: macros can't be undone with Ctrl + Z — save a copy before running one that changes your workbook.
Adds a branded 'Contents' sheet with a clickable hyperlink to every other tab — handy for big workbooks.
Saves every worksheet as a separate PDF next to the workbook — board packs in one click.
Refreshes all data connections and PivotTables and blocks until background queries finish.
Loops bottom-up (so deletions don't shift the loop) and removes fully empty rows.
Define your own worksheet function. Paste into a Module, then use =GrossMargin(B2, B3) in any cell.
Opens every workbook in a folder you pick and stacks their rows into one 'Combined' sheet — tagged by source file.
An event macro that writes the save time into a cell — so a printed or emailed copy always shows how current it is.
The workhorse pattern: find the last used row, then walk down and act on each one.
Freeze a range so its formulas collapse to the numbers they returned — no clipboard needed.
For Each over the tabs — format, clean, or stamp every sheet in one pass, skipping the ones you name.
Loop from the bottom up and delete matching rows — the safe way that never skips one.
Append data from one sheet onto the bottom of another — the consolidate primitive.
Wrap slow code so Excel stops repainting and recalculating on every change — the single biggest speed win.
Pull the whole block into memory in one hit, loop it there, and write it back in one hit — the real speed pattern for big data.
Sum amounts per customer — or build a unique list — with Scripting.Dictionary, the closest VBA gets to a pivot in code.
AutoFilter to the rows you want, copy or process only what's visible, then clear the filter.
Use Range.Find to locate a value — or a column by name — so your macro survives inserted or reordered columns.
One label at the bottom that puts Excel back the way you found it, whether the macro finished or fell over.
Debug.Print, breakpoints and F8 — how to watch a macro run instead of guessing why it didn't.
Stop "subscript out of range" — the most common runtime error in VBA, and it's always this.
A confirmation and an input box — the difference between a macro only you dare run and one the team can use.
A hard-coded path is the first thing that breaks on anybody else's machine.
The poor man's version control, and the honest answer to "which version did we send the bank?"
Read last month's numbers without leaving the file open or creating a link.
Finance data arrives as a cross-tab. Everything downstream wants rows.
The ten minutes of tidying that happens after every refresh, in one keystroke.
PageSetup in code, so every schedule prints the same way without anyone remembering.
You almost never send the model. You send one tab, with the links cut.
The last mile of every monthly routine, and the most-asked VBA task in finance.
Twelve monthly tabs with the same layout, one total — and a warning when one tab isn't the same.
Exports double up. The useful version keys on the right columns and says how many went.
Department A to Z, then amount largest first — and the header argument that ruins it.
The memo field that arrives as "Acme | INV-1042 | 2026-08-31", taken apart in code.
Address columns by their header name, and the macro survives inserted rows and columns.
Set a model's assumptions by name, and a macro becomes a scenario runner.
The audit move: find every number someone typed over a formula in a model they handed you.
Union formats scattered cells in one call. Intersect is how an event knows the edit mattered.
An audit trail beside every input — without the event re-firing itself forever.
Land everyone on the summary, at the top, refreshed — and know it's the first thing IT blocks.
Refresh at 7am before anyone is in — and why that's often a job for a real scheduler.
UserInterfaceOnly — the one flag that lets code write through protection, and that nobody knows exists.
A department-by-month pivot, rebuilt from scratch every month in one run.
One rule instead of forty fragments — the reason an old workbook gets slow.
Split a reference field into parts, reorder them, and rejoin them into something useful.
Month-ends, quarter starts and ages in code — and the locale trap when a date is written back.
Pull an invoice number out of free text when nested SUBSTITUTEs have gone four deep.
Why an email macro works for its author and fails for everyone else — and the fix.
Learn the moves here — or let Wauvel run them on your numbers.
$99/mo, everything included. Free for 14 days, no card.
One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.