**example spreadsheet attached

Weekly Summary Example.xlsx

I have multiple accounts that I keep track of. Each Account is put into a region which has its own tab. Each tab has corresponding dates listed down Column A. Along the rest of the columns are various fields.

My job is to create weekly summaries for each of the accounts.

So far the way I've done this is by creating a "table" (not an actual excel table, but formatted to look like one).
I've listed each of the accounts in the table and I've used a sum function to pull the values for the week.
Along side the table I've listed the row numbers for the range of dates I'm pulling. This is because when I pull down to copy the summary table for next week, the row numbers will auto-populate. I then highlight the formulas and do a 'find and replace' to quickly change all of the formulas correspond with the correct rows from the other tabs.

I know its a mouthful but I think if you look at the spreadsheet you will understand.

Note that I need a new weekly summary every week but I keep the last one so the drag down method seems to be the easiest.

My question is... is there ANY way that is smarter or easier execute this process?
My actual spreadsheet is much larger with a lot more fields for me to pull but this is how it works.

I am even open to organizing the data in a completely different way.

Are there any macros that could pull this? Would a pivot table help?


TL;DR: I need to pull data from various columns on various tabs for a specific range of dates. The dates (by day) are by row.
What is the easiest way to pull and present the data? Spread sheet attached for reference.


Thanks!!