Old Pivot Items causing PivotTable problems?

Engineering Software general discussion Spreadsheets
, ,
#1

Your PivotCache stores EVERY Value that ANY PivotField EVER had.

Some of these can be 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.
How to:

  1. alt+F11 (toggles between sheet and VB Editor)
  2. ctr+R (displays the Project Explorer)
  3. Insert > Module

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 periodically in your workbook containing PivotTables to get rid of all those old unused items.