Loop through only Visible PivotItems

Question

Hi,

I have a pivot table and I have one field in my row labels, the field is called bill_to, in my column labels I have a field called product, and in my values area of the pivot table I have a field called amount.  I'm placing a filter on a product value and the pivot table is then showing me the bill_to clients whom revenue was generated from for those products.  How do I loop through the bill_to column and grab only the bill_to clients that are visible on the pivot table, at the moment I'm using the PivotItem Object but that appears to be looping through all bill_to clients and not just the ones that I can see on the pivot table based on the filters applied.

Thanks.

Simon

Answer 1

The PivotITem object has a Visible property, so within your loop you can check whether the items is visible:

    Dim pvi As PivotItem

    For Each pvi In ...

        If pvi.Visible Then

            ' Your stuff here

            ...

        End If

    Next pvi