Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How To: Check pivot table data source.
Hi TWIMC,
I need to be able to check each pivot table to ensure that each pivot table is using the same source data so that my workbook flie is a small as possible. If the pivot table report is not using the same source, then change it accordingly. Something like, For Each ws In wbk.Worksheets For Each pt In ws.PivotTables pt.SourceData = "DATA!R1C1:" & wsData.Cells.SpecialCells(xlCellTypeLastCell).Addr ess(ReferenceStyle:=xlR1C1) pt.RefreshTable pt.PivotCache.MissingItemsLimit = xlMissingItemsNone pt.PivotCache.Refresh For Each pfd In pt.PageFields For Each rge In pt.PageRangeCells If pfd.Name = ws.Cells(rge.Row, rge.Column).Value And ws.Cells(rge.Row, rge.Column - 1).Value < "" Then pfd.CurrentPage = ws.Cells(rge.Row, rge.Column - 1).Value End If Next rge Next pfd Next pt Next ws But does this way keep my file as small as possible, it with reference to the message that you get when you create a second pivit report but base it on the same data range as the first pivot report, Excel prompt you to select the first pivot table as the data source to save of memory and file space. TIA KM |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Data Source | Excel Discussion (Misc queries) | |||
How to check Pivot Table source? | New Users to Excel | |||
How can I check/verify data in my Data Source for Pivot table? | Excel Worksheet Functions | |||
Pivot table source data | Excel Discussion (Misc queries) | |||
Pivot Table data source "data source contains no visible tables" | Excel Worksheet Functions |