Excel VBA Automation Script

I need a lightweight VBA macro that sits inside an Excel workbook and automates a repetitive reporting task. Each time I open or refresh the file, the script should pull the latest data from the existing sheets, shape it as needed, and then generate a clear summary report—either a pivot-style table, a simple dashboard chart, or both—without any manual clicks.

Scope for this first pass is intentionally modest:
• One well-commented VBA module that runs from a single button or Workbook_Open event.
• Basic data cleansing or reshaping if required to build the report.
• Automatic generation of the chosen chart/table in a separate “Report” sheet, ready to print or export.

Please keep the code readable and note any assumptions directly in comments so I can tweak ranges or sheet names later. A short, step-by-step note on how to install or trigger the macro will round out the deliverable.