VBA to set a page field filter to yes in a pivot table

[Johnny C]

I've got a workbook with a ton of pivot tables, most of which have a page filter based on Month. I want to be able to enter a number in a cell, then run a macro to go through all pivots and 'tick' the value in the list of month values so that the month is included.

This is the code I have. The 3 lines with Visible = True are me trying to get it done diffrent ways. They all give the same error "Unable to set the visible item of the pivotItem class".
What am I doing wrong? 6 pages of Googles say this is what you do.

    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pi As PivotItem
    Dim pf As PivotField
    Dim IntLatestMonth%, strLatestMonth$
    
    On Error Resume Next
    IntLatestMonth = wksData.Range("LatestMonth").Value
    If Err.Number <> 0 Then IntLatestMonth = 0
    On Error GoTo 0
    strLatestMonth$ = CStr(IntLatestMonth)

For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            Application.StatusBar = "Refreshing tab [" & ws.Name & "] Pivot table " & pt.Name
            On Error Resume Next
            pt.RefreshTable
            On Error GoTo 0
            For Each pf In pt.PageFields
                If pf.Name = "Month" Then
                     pf.PivotItems(strLatestMonth).Visible = True
                    For Each pi In pf.PivotItems
                           If pi.Name = strLatestMonth Then pi.Visible = True
                           If pi.Name = strLatestMonth Then pf.PivotItems(strLatestMonth).Visible = True
                    Next pi
                End If
            Next pf
         
            On Error GoTo 0
        Next
    Next

I even tried hard coding it and that didn't work

Sheets("Sales Summary (1)").PivotTables("PivotTable1").PivotFields("Month").PivotItems("4").Visible = True

End Sub

Thanks

 

[RoryA]

Are you trying to add that item to existing selections, or set the filter to that item? You'd use Currentpage for the latter.

 

[Johnny C]

I'm trying to add to existing selections. It's a Year To Date so each month we need a month adding to the existing (multiple) selection. There are other filters where the whole year is populated and we only want up to the current month.

 

[RoryA]

First obvious question: what code do you get recording a macro doing it manually?

 

[Johnny C]

This is what I got:-

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Month")
        .PivotItems("4").Visible = True
    End With

I saw a post on StackOverflow that said basically you can't just add to a prexisting multiple selection - what you need to do is clear the existing filters and add a new one with allow multiple filters set and rebuild them. I'm trying that at the moment

 

[RoryA]

That's not generally true. It is usually quite possible to select another additional item using code such as the code you posted. Does the code you recorded work when you run it?

 

[Johnny C]

No,
if I try

If pf.PivotItems("12").Visible = False Then

it says unable to get the PivotItems property of the PivotsField class

When I try and set the visible property it says "Unable to set the visible item of the pivotItem class".

 

[RoryA]

So the code from post 5 fails?

 

[Johnny C]

Even that does, yes. I even tried setting
ActiveSheet.PivotTables("PivotTable1").PivotFields("Month").CurrentPage = "(All)" to see if I could deselect what I didn't want but it wouldn't do that.

 

[RoryA]

What about ActiveSheet.PivotTables("PivotTable1").PivotFields("Month").clearallfilters?