VBA: Removing old items remaing in Pivot Field Dropdowns
Have you noticed that in Excel 2002 or later even if you replace the source data for the pivot table, the pivot table retains items that do not exist anylonger (in the dropdowns)?
For example, some sales reps may leave the company, and the names of their replacements appear in the source table. Even after you refresh the pivot table, the names of the old sales reps will appear, along with the new names.
To prevent missing items from appearing, or clear items that have appeared, you can used the following VBA code:
Sub DeleteMissingItems2002All()
‘prevents unused items in non-OLAP PivotTables
Dim pt As PivotTable
Dim ws As Worksheet
Dim pc As PivotCache
‘change the settings
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
Next pt
Next ws
‘refresh all the pivot caches
For Each pc In ActiveWorkbook.PivotCaches
On Error Resume Next
pc.Refresh
Next pc
End Sub
