Excel VBA 2016 list selected items of a page filter into a worksheet range

Question

I am writing a VBA code in Excel 2016 that would save user's customised pivot table layout into csv, and then load it back when they need.

My pivot table is a PowerPivot one.

"Market" field has many countries to select from. It is in page filter area, and filtered to show France and Spain.

What I need to achieve: in a worksheet, list the items selected to be displayed.

Here is the code (one of many I tried):

Sub test_3()

Set PT = ActiveSheet.PivotTables(1)

For Each PF In PT.PivotFields
    If PF.Orientation = xlPageField Then
        Debug.Print PF.Name
        For i = 1 To PF.PivotItems.Count
            If PF.PivotItems(i).Visible = True Then Debug.Print PF.PivotItems(i).Name
        Next i
    End If
Next PF

End Sub

Output I get:

[Markets].[Market].[Market]

which is just a cube reference to the field name.

Seems like PivotItems doesn't work with PageField.

All examples I've so far seen, use this property, but they are quite old and probably use non-PowerPivot tables.

Can you please change the line Debug.Print PF.PivotItems(i).Name to Debug.Print PF.PivotItems(i).Value and see the result? — J.B.
@J.B. i have tried, it didn't work. PF.PivotItems.Count seems to always be zero, so it never got to that line. What i tried, i hardcoded FROM i=1 to 5 to get into the loop, and then got an error "Unable to get PivotItems property of the PivotField class on the IF condition part. Any ideas? — Lana B

Lana B Lana B · Accepted Answer · 2017-04-26T17:25:28

I found a solution posted by Andrew Poulsom and another guy here.

Basically, passing the visible items into an array, and then refer to its members in a loop:

Dim temparray as Variant
.... some code setting your pivot field PF object reference
temparray = PF.VisibleItemsList
For i = LBound(temparray ) To UBound(temparray )
   activesheet.cells(i,1) = temparray(i) 
Next i

Then, to load the filter with that range, I collected the cells range into an array (in a separate procedure that loads pivot tables).

temparray = Application.Transpose(your_range_ref)
PF.VisibleItemsList = Application.Transpose(temparray)

If anyone has a better solution, please share.

Excel VBA 2016 list selected items of a page filter into a worksheet range

1 Answers