How do you get a TRUE refresh of a Pivot table

[rob nobel]

Oh Pivot tables are so good and boy, do they save on calc time and size of
file!
BUT.........I notice that if some of the data it relies on is changed, by
some magic, it still remembers the old data and, that old data will also be
shown in the table unless you physically delete those references using the
drop downs.
I have found that sometimes you can clear the old references by re-running
the wizard backwards as it seems to refresh those references. But that
doesn't work in all situations.
Is there a way to remove old references automatically AND how is it possible
that they still exist? In fact, if I delete all the data to which the Pivot
refers and refresh the Pivot, the dropdown list still shows all the items
that used to be there!????
Rob

 

[Ken Wright]

From a previous post of Debra Dalgleish's:-

Sub PivotTableRefresh()
'Debra Dalgleish
'gets rid of unused items in PivotTable
' based on MSKB (Q202232)
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim i As Integer

On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
For Each pf In pt.PivotFields
For Each pi In pf.PivotItems
If pi.RecordCount = 0 And _
Not pi.IsCalculated Then
pi.Delete
End If
Next
Next
Next
Next
End Sub

 

[Debra Dalgleish]

There are instructions here for clearing old items from the dropdown lists:

http://www.contextures.com/xlPivot04.html

 

[rob nobel]

Thanks for that Ken, it surely had me frustrated. I would still like to
know (if you can help), why and how that data can still be there. Without
the code you suggested, the pivot table is innacurate unless you study it to
look for errors and clean them out using the drop downs (which a lot of
people would not do).
It's puzzling that data that has been changed can still be shown, which
causes some duplication of results.
I hope this problem is addressed on later versions as mine is v2000.
Rob

 

[rob nobel]

Hi Ken. Thanks again for this info from a previous post.
It seems to work quite well except the (blank) item seems to portray the sum
of the data that's been deleted (if all data has been deleted), even after
running this code. This is not a huge problem but doesn't look too
professional.
In short, the (blank) item cannot be unticked if it's the only one remaining
and shows the sum total of what all the other items were.
There are 2 other problems related to Pivot Tables which I'll post
separately.
Rob