Excel Cash Template Automation Script

I regularly receive Cash Template workbooks from different clients in Excel 365. The files always contain expense reports, yet the number of rows varies from client to client. Each time a file arrives I need the data automatically:

• sorted into the correct column order
• arranged so that headings, dates, descriptions and reference numbers line up with my accounting-system import sheet
• subtotalled and a grand total added at the bottom of the full sheet

Because the layout is consistent but the record count changes, a simple recorded macro breaks. I therefore need a robust Script that detects the last used row/column and adjusts itself dynamically.

Key points
• Environment: Microsoft Excel 365
• Source data: Expense reports only
• Focus: sorting, arranging and automatic totals (no error-flagging routine required)
• Trigger: ideally a single button or Workbook_Open event so I can run it the moment a client file lands in the folder.

Deliverables
1. An unlocked .xlsm template containing the reusable macro.
2. Clear instructions inside the workbook (or a short read-me) showing where to paste a new client sheet and how to run the script.
3. Optional—but appreciated—brief comments in the code so I can tweak column names in future.

Once the Script processes the sheet I should be able to copy-paste (or import) the tidy data straight into my accounting system without further edits. If anything here is unclear, let me know and I’ll provide a sample Cash Template.