Remove Missing items from pivot table in entire workbook.

'**********************************************
Author: Debra Dalgleish, Contextures
http://www.contextures.com/tiptech.html
http://blog.contextures.com/ '
'***********************************************
Sub DeleteMissingItemsAll()

'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

---

Love Cricket? Check out live scores, photos, video highlights and more. Click here.