pivot table page field hidden items

M

marwan

Is there a vba code that can determine the "pivot table page field
hidden items"?
I tried the "visible" property for the page field items. It returned
"True" only for the "CurrentPage" item and "False" for all the others.
 
D

Debra Dalgleish

You could set each item as the current page. Those that are hidden will
create an error, instead of becoming visible. For example:

'========================
Sub PageItemsHidden()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim piCurr As String

Set pt = ActiveSheet.PivotTables(1)
Set pf = pt.PivotFields("Rep")
piCurr = pf.CurrentPage
For Each pi In pf.PivotItems
On Error Resume Next
pf.CurrentPage = pi.Name
Debug.Print pi.Name & " - " & pi.Visible
Next pi
pf.CurrentPage = piCurr
End Sub
'===========================
 
M

marwan

Thank you Debra for your idea. But is this the only solution?
In fact, changing the CurrentPage of the page field will cost me a lot
of time and processing resources just to know what are the visible
items in my page field.
I tried something like that:

iserror(ActiveSheet.PivotTables("PivotTable1").PivotFields("xx").CurrentPage
=
ActiveSheet.PivotTables("PivotTable1").PivotFields("xx").PivotItems("yy").name)

but it gave me always "True" regardless the pivot item "yy" is hidden
or not.

Thanks again
 
Top