Set all data option for all Pivot Tables in Workbook to "None" from "Automatic"

[pliskers]

Rather than do them one at a time, I'd love to have some VBA that would run through all tabs of a workbook and, for each pivot table, change the data option to "None" instead of "Automatic" so that unused items are not retained in the cache, and then refresh the pivot table.

Is this possible in a single piece of code?

 

[RoryA]

Try this:

Sub Refresh_All_Pivots()
   Dim pc As PivotCache

   For Each pc In ActiveWorkbook.PivotCaches
         pc.MissingItemsLimit = xlMissingItemsNone
         pc.Refresh

   Next pc

End Sub

 

[pliskers]

Try this:

Sub Refresh_All_Pivots()
   Dim pc As PivotCache

   For Each pc In ActiveWorkbook.PivotCaches
         pc.MissingItemsLimit = xlMissingItemsNone
         pc.Refresh

   Next pc

End Sub

Perfect, thank you!