Is this possible?? Pivot tables

[BrendanJenman]

I have 70 pivot tables that run off a database query of varying number of rows from 1000 to 5000 rows when refreshed. Initially I built the pivotables as copies of eachother so that when I refresh one, they all automatically refresh. However, I made an error. I set the source range to $A:$Z which means the memory used to process all the rows is massive because it reads all cells to the bottom of the spreadsheet. So now I have a huge workbook, which cause problems for various reasons.

I would now like to change the source range of each pivot table to a named range which can handle varying amount of data - this is no problem and will significantly reduce the size of the file.

EXCEPT: - The pivot tables are will no longer be linked to eachother as copies of each other which means I would have to refresh each one when the data changes. There are 70 tables, something I obviously want to avoid.

Is there a way of either changing the source range of all pivottables so that they stay linked to eachother or a way to link all the pivot taables to eachother after I have changd their source data range so that they all refresh together?

Any help would be amazing as I have wracked my brains all day

Thank you
Brendan