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