How To: Check pivot table data source.

  • Thread starter Kevin McCartney
  • Start date
K

Kevin McCartney

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).Address(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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top