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]

That works.

I then recorded this

   With ActiveSheet.PivotTables("PivotTable1").PivotFields("Month")
        .PivotItems("7").Visible = False
    End With

I then changed the "7" to "8" and ran it and it crashed with the above error.

The only thing and this is weird. when I run this

                   For Each pi In pf.PivotItems
                        Debug.Print ws.Name & "-" & pt.Name & "-" & pf.Name & "-" & pi.Name
                        Debug.Print pi.Visible
                    Next pi

it returns TRUE for each item though I can see they're not checked.

 

[RoryA]

What's the data source for the pivot table?

 

[Johnny C]

I reran it and it has now cleared all filters (and all are false on the debug.print) but it doesn't like 'AllowMultipleFilters=TRUE

 

[Johnny C]

The data source is 'Data File'!$A$6:$AX$1045584
I was going to convert that to a table but as it's not my file I decided not to.

 

[Johnny C]

                        pf.ClearAllFilters
                        pf.AllowMultipleFilters = True

First line works second doesn't "Object doesn't support Property or Method

 

[RoryA]

I think you were looking for pf.EnableMultiplePageItems = True

 

[Johnny C]

Great, thanks Rory. I'll try that. The AllowMultipleFilters is odd, the MS documentation says it allows a field to have multiple filters but it's set at pivot table level. Which I assume means all field can have multiple filters which you may not want.

 

[Johnny C]

it stopped crashing briefly but didn't set anything. It has added 2 instances of 4 to the selection panel in the process, which Refresh doesn't get rid of.

Is there some way to clear all the caches then refresh ? It got to the stage where refresh closed Excel down immediately. And if you loop through Pivotfield items (For each pi in pf.pivotitems) it goes through them twice and adds a blank.

This is the current code:-

    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
            
'-------------------------------------------------------------------------------------------------------
            If DoMonths Then
                For Each pf In pt.PageFields
                    X1 = ws.Name
                    X2 = pt.Name
                    X3 = pf.Name
                    X5 = pf.CurrentPage.Value
                    If pf.Name = "Month" Then
                        pf.ClearAllFilters
                        pf.EnableMultiplePageItems = True
                        pf.CurrentPage = "(All)"
                        With pt.PivotFields("Month")
                            For Each pi In pf.PivotItems
                                X4 = pi.Name
                                X6 = CLng(pi.Name)
                                If CLng(pi.Name) <= IntLatestMonth Then
                                    Debug.Print ws.Name & "|" & pt.Name & "|" & pf.Name & "|" & pi.Name & "|" & pi.Visible
                                    .PivotItems(pi.Name).Visible = True
                                Else
                                    .PivotItems(pi.Name).Visible = False
                                End If
                            Next pi
                        End With
                    End If
                Next pf
            End If
'----------------------------------------------------------------------------------------------------
            On Error GoTo 0
        Next
    Next
    
    Application.StatusBar = False
    Application.DisplayStatusBar = oldStatusBar
    
    MsgBox "All Pivot Tables refreshed"

End Sub

 

[Johnny C]

Right. This is finally solved. All the stuff on Google which just says set the visible property to false or true is bunkum. It's a lot more complex. If you have a big legacy pivot table Excel retains a memory of items in the drop down selection boxes. You may only see what is there now, but when you're using VBA it accesses the full list. And for some reason there can be duplicates. So I was looping through the field items. I changed the first instance properties to visible = True. Then it brought the second instance and because the property was set for an identical item it tested the value from the first instance and didn't set it. But then the dropdown selection box was using (I think) the second instance which had the original value. Confusing.

Anyway thanks to Debra from contextures for leading me through the minefield! You need to clear filters which sets them all to Visible = True then set Visible = False for the ones you don't want.

Here's the full code.

Public Sub refreshPT()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pi As PivotItem
    Dim pf As PivotField
    Dim IntLatestMonth%, strLatestMonth$
    
    oldStatusBar = Application.DisplayStatusBar
    Application.DisplayStatusBar = True

    DoMonths = True
    Call DeleteMissingItemsAllPTs

    On Error Resume Next
    IntLatestMonth = wksData.Range("LatestMonth").Value
    If Err.Number <> 0 Then IntLatestMonth = 0
    On Error GoTo 0
    strLatestMonth$ = CStr(IntLatestMonth)
    
    Application.StatusBar = False
     
    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
            
'-------------------------------------------------------------------------------------------------------
            If DoMonths Then
                For Each pf In pt.PageFields
                    X1 = ws.Name
                    X2 = pt.Name
                    X3 = pf.Name
                    X4 = pf.CurrentPage.Value
                    If pf.Name = "Month" Then
                        pf.ClearAllFilters
                        pf.CurrentPage = "(All)"
                        pf.EnableMultiplePageItems = True

                        With pt.PivotFields("Month")
                            For Each pi In pf.PivotItems
                                X5 = pi.Name
                                If pi.Name <> "(blank)" And pi.Name <> "(All)" Then
                                    If CLng(pi.Name) > IntLatestMonth Then pf.PivotItems(pi.Name).Visible = False
                                End If
                            Next pi
                        End With
                    End If
                Next pf
            End If
'----------------------------------------------------------------------------------------------------
            On Error GoTo 0
        Next
    Next
    
    Application.StatusBar = False
    Application.DisplayStatusBar = oldStatusBar
    
    MsgBox "All Pivot Tables refreshed"

End Sub
Sub DeleteMissingItemsAllPTs()

'For NEW versions of Excel

'developed by contextures.com
'to prevent unused items in
'   non-OLAP PivotTables
'   changes MissingItemsLimit setting
'   for all PTs in active workbook
Dim pt As PivotTable
Dim wb As Workbook
Dim ws As Worksheet
Dim pc As PivotCache

Set wb = ActiveWorkbook

'change the settings
For Each ws In wb.Worksheets
  For Each pt In ws.PivotTables
    pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
  Next pt
Next ws

'refresh all the pivot caches
For Each pc In wb.PivotCaches
  On Error Resume Next
  pc.Refresh
Next pc

End Sub

 

[RoryA]

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.