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

 

[Johnny C]

You can turn off the option to retain old data items. Also, you can't have duplicate items in one field - there must be something different about them.

In theory Rory, you shouldn't be able to have duplicate items. But looping through I was getting duplicates due to legacy values. I was changing the visible value. When the item came up on the second time, visible had been reset to the value it had been set to the first time. But in the pivot table, it hadn't. When I stripped the legacy values it worked. Im assuming the field value is stored in the calculation tree and XL gets confused over multiple duplicate entries setting the value to the first instance it can find, even though the pivot is using the last instance. I don't know the Excel code but it looks like a mismatch in pointers.

 

[RoryA]

I suspect you are talking about the situation where you have numbers and the same numbers but stored as text in the source data. There is no way in VBA (as far as I know) to distinguish between the two since all the relevant properties return a String, but they are actually stored as different values, which is why they appear more than once.

 

[Johnny C]

That would explain it Rory, thanks.