Can't find selected multiple values in report filter

[bb_89_gd]

Hi. First time poster.

I have a pivot table named summaryTable2 generated by VBA and have created a report filter via VBA for the field "Date". The filter was generated with the code

With ActiveSheet.PivotTables("summaryTable2").PivotFields("Date")
.Orientation = xlPageField
End With

I have been looking around forums etc trying to find a way to list out the selected values for the date filter. I have had no luck. Currently I am using the below code.


For Each date_filter In Worksheets("Running Totals").PivotTables("summaryTable2").PivotFields("Date").PivotItems

If date_filter.Visible = True Then

Cells(i, 3).Value = date_filter

i = i + 1

End If

Next date_filter


The code is running without error. However date_filter.visible always remains false, even when I select specific values in the drop down date filter. So nothing is listed in the chosen cells.

Some points to note.

The code works fine if date is chosen as a Row filter (it will list out the chosen values from the filter)

Date_filter is declared as a pivot item. If I put in the code date_filter.visible = true, the code will run but immediately after that line, date_filter.Visible reverts to False.

I have tried putting

ActiveSheet.PivotTables("summaryTable2").PivotCache.MissingItemsLimit = xlMissingItemsNone

At the start of the code and also tried refreshing the pivot tables. Nothing is working.

I've also tried changing 'PivotFields' to 'PageFields' in the For statement and it still doesn't work.

Any help would be much appreciated.

PS posting from mobile sorry if code isn't in right format.