Pivot Cache - Massive Spreadsheet

Hi,

I'm really struggling with a large spreadsheet I'm developing. It has several (dozen) pivots within the ss. I created most of them by dragging and dropping the previous pivot table. This seems to have been a good idea because the ss is around 8Meg.

However, I wish to adjust the ranges on the pivots. When I do this the spreadsheet rapidly becomes MASSIVE. I suspect it's because Excel is duplicating pivot cache.

How can I repoint all the related pivots at the same pivot cache? I've been trying to muck around in VBA but I can't even find how to repoint existing pivots (not sure if it's possible). I'd really appreciate some help.

Thx.

There's sample code here for identifying and changing the pivot cache:

http://www.contextures.com/xlPivot11.html

Wyvern wrote:
> Hi,
>
> I'm really struggling with a large spreadsheet I'm developing. It has
> several (dozen) pivots within the ss. I created most of them by
> dragging and dropping the previous pivot table. This seems to have
> been a good idea because the ss is around 8Meg.
>
> However, I wish to adjust the ranges on the pivots. When I do this the
> spreadsheet rapidly becomes MASSIVE. I suspect it's because Excel is
> duplicating pivot cache.
>
> How can I repoint all the related pivots at the same pivot cache? I've
> been trying to muck around in VBA but I can't even find how to repoint
> existing pivots (not sure if it's possible). I'd really appreciate
> some help.
>
> Thx.
>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Worked a charm my spreadsheet has gone from a massive 150+mb to a slim trim 3mb ... Many thanks.

You're welcome, and thanks for letting me know the results.

Wyvern wrote:
> Worked a charm my spreadsheet has gone from a massive 150+mb to a slim
> trim 3mb ... Many thanks.
>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html