Pivot in excel is most useful functionality to aggregate and summarize huge data. It is one the most useful reporting tool in excel which used to represent meaningful data from a large data set. Many a times back end data get refreshed for these pivots. Which generates missing items in pivot table which not require anymore.
Removing missing items from various pivots sometime would be a quite time consuming task.

Here is the code which will make this task very easy,

Sub Remove_Missing_Pivot_Items()
' Author: Dreams24
' Written for VBA Tricks and tips blog
' https://www.vbatricksntips.com


'Removes unused items in PivotTables and Refresh cache

Dim pTable As PivotTable
Dim wSheet As Worksheet
Dim pCache As PivotCache

'Changes Pivot Cache settings for each pivot table in active workbook

For Each wSheet In ActiveWorkbook.Worksheets
        For Each pTable In wSheet.PivotTables
               pTable.PivotCache.MissingItemsLimit = xlMissingItemsNone
        Next pTable
Next wSheet

'Refreshes caches for all pivot table in activeworkbook

For Each pCache In ActiveWorkbook.PivotCaches
On Error Resume Next
        pCache.Refresh
Next pCache

MsgBox "Unused items delelted from all pivots", vbInformation

End Sub