Project: Automating Month-End in Excel with VBA (Written by AI, Tested by Me)
Month-end closing has a dirty secret: a lot of it is copying. Copy the ledger into the summary. Copy closing balances into next month’s opening. Create the new month’s cash workbook from the old one. None of it requires judgement — it just requires not making mistakes at 9 PM. So I automated it with three VBA macros, built through conversation with an AI assistant and tested carefully on copies before touching real files.
Macro 1: Ledger to summary, automatically
The first macro reads ledger entries and populates a month-wise summary sheet. The tricky part was matching account descriptions that aren’t perfectly consistent — vehicle maintenance entries, for example, appear under slightly different names. Wildcard matching solved it: the macro finds the right row even when the wording varies.
Macro 2: Advance invoice rollover
The second handles advance-invoice tracking at month end: rows are kept or removed based on their status, and closing balances carry into the new month. This one taught me the classic automation bug — my first version copied balances after rows had been deleted, corrupting the transfer. The fix was taking a snapshot of the data first, then doing the cleanup. Order of operations matters more in automation than in manual work, because the computer won’t notice something looks wrong.
Macro 3: The new cash workbook
The third macro creates the new month’s Cash on Hand workbook in one click. It deletes old transaction rows properly (Excel Tables need special handling through the ListObject — normal row deletion breaks them), clears literal values while preserving structural formulas, and re-points external links to the correct new month’s petty cash file. That last part alone used to be a reliable source of #REF! errors.
What I’d tell other accountants
You don’t need to know VBA to get VBA. You need to describe your workflow precisely — which sheet, which columns, what happens to each row — and then test the generated macro on a copy of your file, several times, before trusting it. The AI writes the code; you provide the domain knowledge and the skepticism. Together, that combination turned hours of careful copying into a few clicks.
