Excel VBA macros to automate manual monthly reporting

Every month the team spends almost two days manually assembling the same operational report. Data arrives in a dozen separate tabs from different branches, and someone has to copy each one into the master sheet, fix column order, remove blank rows, recalculate a few summary columns and apply the same formatting all over again. It's tedious, error-prone and always falls on the busiest week. I want to hand over all this routine to a set of Excel VBA macros so that one button turns raw data into a polished formatted report.

The file is a regular desktop Excel workbook (Windows, .xlsm) and I want to keep it that way. This is exactly a VBA project, not Google Sheets or Apps Script. Specifically the macro should go through all source sheets, consolidate them into a single consistent table, perform validation and highlight missing data, incorrect date formats and duplicate IDs, then generate a clean summary report with headers, number formatting, conditional highlighting and a couple of slices in the style of pivot tables. I also want a small custom form where a non-technical employee selects the reporting month and folder with source files, plus buttons on the control sheet to run each step or the entire process at once.

I ask for readable code with comments and reasonable error handling: if the source file is corrupt, the macro should tell you what's wrong rather than crash. I'll provide an anonymized sample workbook and the exact target layout. Ideally the result saves us several hours monthly and completely eliminates manual copy errors.

— Consolidate multiple sheets into a single master table
— Data validation: missing entries, incorrect dates, duplicate IDs
— Auto-formatted summary report with conditional highlighting
— UserForm and buttons on control sheet for non-technical users
— Commented code with error handling and brief instructions