Someone passed me a workbook which contains many pivot tables on
separated sheets. After examining those pvt, I found they all point to
exactly same data source but have different pivot cache, which makes
the workbook unnecesssarily large (10MB). My question: how can I assign
all pvt to just one single pivot cache to save space without start
building pvts all over again?

Thanks.

Huyeote


--
Huyeote
------------------------------------------------------------------------
Huyeote's Profile: http://www.excelforum.com/member.php...o&userid=13894
View this thread: http://www.excelforum.com/showthread...hreadid=270005

You need to change the referance of all pvt except the first to th
first pvt table. That will reduce the size of file also.
While creating the pivot table the first step in the wizard is t
choose the base data with 4 options. Choose last one ("Another pivo
table or pivot chart" for all the subsequest pvt using the same dat
range.
Thanks,
Sirin.


Huyeote Wrote:
Someone passed me a workbook which contains many pivot tables o
separated sheets. After examining those pvt, I found they all point t
exactly same data source but have different pivot cache, which make
the workbook unnecesssarily large (10MB). My question: how can I assig
all pvt to just one single pivot cache to save space without star
building pvts all over again?

Thanks.

Huyeot

--
siri
-----------------------------------------------------------------------
sirin's Profile: http://www.excelforum.com/member.php...fo&userid=1528
View this thread: http://www.excelforum.com/showthread.php?threadid=27000

try:

Sub AllToCahceOne()
Dim ws As Worksheet
Dim pt As PivotTable

For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
If pt.CacheIndex < 1 Then pt.CacheIndex = 1
Next
Next

MsgBox "Number of PivotCaches: " & _
ActiveWorkbook.PivotCaches.Count

End Sub


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Huyeote wrote:


Someone passed me a workbook which contains many pivot tables on
separated sheets. After examining those pvt, I found they all point to
exactly same data source but have different pivot cache, which makes
the workbook unnecesssarily large (10MB). My question: how can I assign
all pvt to just one single pivot cache to save space without start
building pvts all over again?

Thanks.

Huyeote