Changing Range on Pivot Cache

Hi,

I would like some help please. I have a swag of pivot tables (that I've structured to all point to the same pivot cache). As part of a regular process I need to assign this cache to point to new Data files and new Data ranges.

I've found a pice of code that seems to do it when you are creating a new pivot cache (using SourceData:= Range("A1").CurrentRegion.Address) but I'm not sure how to modify an existing pivot range.

Can anyone please help?

Many thanks,

Geoff

Hi All,

Just in case anyone experiences this problem too. I did find out how to do it. But my final solution was to create my own pivot cache and then spawn all the required pivot tables (2000+) from the created caches.

Whilst it did take me quite a while to figure out how to do this, it proves a far more robust solution than creating pivot tables manually and modifying the cache. They tend to behave in strange ways sometimes.

Geoff