Change source data for multiple Excel pivot tables but not using cache -

Change source data for multiple Excel pivot tables but not using cache -

i have reply hundreds of questions same source data, 85mb file (about 300k rows). i've created pivot tables reply questions in separate file source info create manageable.

my problem after i've set bunch of pivot tables, notice need create column in source info (to grouping info filter), affects range used in pivot tables. happens if utilize named range source data.

because have many pivot tables , source info huge, not want utilize pivot table cache. methods i've seen on how alter source info range relies on cache. there way without cache or utilize cache turn off?

i'm using excel 2010.

a pivottable has built on top of pivotcache. can't separate 2 , still have pivottable.

could add together filter field end of info set existing pivotcache doesn't automatically pick up, create new pivot table using new larger range? create 2 pivotcaches, 1 original 1 without filter, , new 1 filter. downside method pivotcaches take disk space , bloat size of sheet. cache same size of info it's based on, double size of worksheet.

excel excel-vba