Hello,
Excel and VBA provide all the necessary tools to read the data in the exact format you want, so this is not a problem. All the steps you are describing can be fully automated via VBA, even the data transferring from the pivots to the reporting template, and of course this can be developed to handle the data dynamically, independently of the data set derived each time.
A few words about me: I'm developing VBA programs for more than 15 years and I'm a trainer of Excel and VBA for 12+ years. As you can guess, I have created a plethora of VBA programs of any kind, reports, dashboards, pivots, ... for both training purposes and business.
Looking forward to hear from you,
Kind regards,
Chriskerpini