I'm trying to implement one-click pivot tables filtration (similar to this one). It works great on single items but for some reason not on multiple items:
Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
Dim SourcePivotItem As PivotItem
For Each SourcePivotItem In Target.PageFields(1).PivotItems
If SourcePivotItem.Visible Then _
MsgBox SourcePivotItem.Name + " is visible"
' Message box never pops when EnableMultiplePageItems=True
Next SourcePivotItem
End Sub
Any ideas? Should I use different property on multiselect?
p.s: I'm using Excel 2013.
Random things I've tried that did not work:
Target.PivotCache.MissingItemsLimit = xlMissingItemsNone
Target.PivotCache.Refresh
Target.RefreshTable