Deleting the Pivot Cache in Excel

In order to keep your Excel file at its leanest, it's a good idea to routinely clean out the pivot cache when your data in your source changes.  If you don't, you'll notice that names in your pivot fields that are older and no longer exist in your new data still appear in your pivot field lists. 


To clear your pivot cache, run this macro: 


  Sub DeletePivotCache() 
  Dim pt As PivotTable
  Dim ws As Worksheet 
  For Each ws In ActiveWorkbook.Worksheets
    For Each pt In ws.PivotTables
      pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
      pt.PivotCache.Refresh
    Next pt
  Next ws 
  End Sub