Automate Monthly Excel Sales Workbook

I have a 19-sheet Excel workbook that our team updates every day. Each tab represents a different sales area; every entry is typed in by hand with the date, customer name and sale amount, and a separate “Totals” sheet rolls everything up.

At month-end I still clear out all 19 tabs manually, copy the numbers to a static archive file and rebuild the summary for the new month. It’s clunky, slow and error-prone, so I’d like the whole process tightened up.

Here’s what I need the re-engineered workbook to handle for me:

• When I trigger “Close Month”, the workbook should:
– Capture every sheet’s data into a read-only archive (one archive per month, stored in a visible, easy-to-browse way).
– Lock that archive so past months stay intact and instantly viewable.
– Reset each live sheet to a clean template, zeroed out and ready for fresh entries.

• At the same time it should generate updated monthly reports and overall totals automatically—ideally through PivotTables, Power Query or another method you trust—so I no longer have to rebuild formulas.

• All of this must work without breaking our simple workflow of typing new rows directly into each area sheet.

Please look over the current file, propose how you’d streamline it (VBA macros, Power Query, formulas, buttons, etc.) and outline the key steps you’ll deliver. I’m happy to answer questions or run test versions so we land on a solution that saves clicks and keeps every past month one click away.