Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Old Pivot Items causing PivotTable problems

PivotTable Tips

Old Pivot Items causing PivotTable problems

by  SkipVought  Posted    (Edited  )
It seems that something called the PivotCache, stores every value that each PivotField EVER had.

Sometimes, these are invalid values, like blanks in date fields, for instance, and these invalid values render features like Grouping unuseable.

Don't throw up your hands in surrender. Clean your PivotCache instead. Paste this little function into a MODULE in the Visual Basic Editor. [tt]
How to:
1. alt+F11 (toggles between sheet and VB Editor)
2. ctr+R (displays the Project Explorer)
3. Insert > Module
[/tt]
Code: 
Sub CleanMyPivots()
'SkipVought/2007 Apr 30
'--------------------------------------------------
'cleans up pivot tables having OLD/UNUSED data hanging around.
'--------------------------------------------------
    Dim pc As PivotCache
    For Each pc In ActiveWorkbook.PivotCaches
       pc.MissingItemsLimit = xlMissingItemsNone
       pc.Refresh
    Next
End Sub
Just run this macro in your workbook to get rid of all those unused items.
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top