Automate Daily Metrics in Excel

My team tracks key numbers in a master spreadsheet that lands on my desktop every morning. Right now I spend far too long copying the newest tab into yesterday’s workbook, merging rows, refreshing formulas, building trend calculations, and dressing it all up with charts before anyone even sees the numbers.

I need a VBA-driven (or Power Query / Power Automate if you prefer) solution that will:
• pull the fresh data from the incoming Excel file,
• append it to our historical sheet, refreshing all formulas and trend calculations automatically,
• generate a clean, date-stamped daily report file, and
• fire off a concise email summary (totals and key variances) to our distribution list each morning.

The heaviest time sinks for me today are the copy-and-combine steps, the rolling total and trend math, and the consistent chart and formatting work—so those all need to be hands-free when you’re done.

Please build the automation so that scheduled execution is possible (Task Scheduler or equivalent) and leave clear, commented code so I can tweak ranges or add metrics later. Once the workflow runs end-to-end on my machine and the email summary lands in my inbox without manual touch, we’re good to sign off.