Automating the Weeks Selected in Multiple Pivot Tables

[Indy1977TX]

I have a weekly report and there is a specific manual processes that feels like could be automated. Since managers see this file, I would prefer no macros in the solution to avoid the managers from getting marco warnings when opening the file. No macros is a preference, not a stipulation.

Today is a Monday (3/27/23) so it is time for me to paste reports into to my Excel based dataset then open another Excel file that pulls that data into PivotTables which get cleaned up and combined on other tabs for management review.

My pivot tables select from a range of the last 9 Mondays (including today) so today those Mondays are 1/30, 2/6, 2/13, 2/20, 2/27, 3/6, 3/13, 3/20, & 3/27.
Before I start the next part, I apologize for not renaming my PivotTables no one looks behind the curtain, so it has never been pressing.
PivotTable12 & PivotTable11 use the oldest 8 weeks in the above list (1/30, 2/6, 2/13, 2/20, 2/27, 3/6, 3/13, & 3/20).
PivotTable15, PivotTable17, PivotTable18, PivotTable1, & PivotTable1 (different tab) use the most recent 8 weeks in the above list (2/6, 2/13, 2/20, 2/27, 3/6, 3/13, 3/20, & 3/27).
PivotTable4, PivotTable6, & PivotTable11 (another duplicate name in same file on different tab) use the current Monday date only (3/27).

Currently I click into the data range on each PT selecting the newest week of data and unselecting the oldest week that is rolling off. It would be nice to update the date range in one place instead of 11 places (one cell and 10 PTs).

Since this is my first post here, for reference, I would place myself as a medium Excel user. Been using it close to 30 years. Took multiple classes in that first decade. Then, I had a job for about a year mostly focused in Excel development, but that was 19 years ago so even though I use Excel regularly, I am rusty on my advanced skills like programming. Just learned on Friday that I could refresh all 10 of these PTs at once LOL

Using Excel 365, by company mandate, which is my least favorite version of Excel ever. I just updated my report in the management review file and it failed to upload/save. So, closing it all down to redo all this PT updating again.

All the best,
Jordan

 

[awoohaw]

I'd suggest making those initial import targets tables, and make sure your pivot tables are using the tables for their calcuations.
Then use slicers to choose your week ranges (but, I think if you have the same data source definition for ALL pivots you may need to change some - first 8 weeks, vs last 8 weeks, etc.).