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.


--
Wyvern
------------------------------------------------------------------------
Wyvern's Profile: http://www.excelforum.com/member.php...fo&userid=5743
View this thread: http://www.excelforum.com/showthread...hreadid=518063

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.


--
Wyvern
------------------------------------------------------------------------
Wyvern's Profile: http://www.excelforum.com/member.php...fo&userid=5743
View this thread: http://www.excelforum.com/showthread...hreadid=518063

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