Pivot caches?
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 |
Pivot caches?
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 |
Pivot caches?
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 |
All times are GMT +1. The time now is 12:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com