ExcelBanter - View Single Post - VBA Filtered Pivot Table Showing "Multiple Values Selected"; Only One Value Selected

KeriM

I'm trying to loop through my pivot table items and filter on the value that matches a named range on another sheet. The looping and filtering works as expected. The problem is that when it's finished, although the pivot table is on the correct filtered value (and only one value is selected), it still shows "multiple selections" in the caption. Here is my code:

Code:

    Dim pt As PivotTable
    Dim Field As PivotField
    Dim Item As PivotItem    

'Filter based on previous workday's date
        Sheets("Note_Type_Summary").Select
        Set pt = Sheets("Note_Type_Summary").PivotTables("PivotTable1")
        Set Field = pt.PivotFields("NOTES_ENCOUNTER_DATE")
        pivotfiltervalue = Format(Sheets("Summary 25 Work Day").Range("RangeName"), "m/d/yyyy")
        Field.EnableItemSelection = False
        Field.ClearAllFilters
        
        For Each Item In Field.PivotItems
            Item.Visible = (Item.Caption = pivotfiltervalue)
        Next Item

As an aside, this line here works fine instead of looping through each item:

Code:

Sheets("Note_Type_Summary").PivotTables("PivotTable1").PivotFields("NOTES_ENCOUNTER_DATE").CurrentPage = pivotfiltervalue

However, it will only work for me. When my coworker tried to run this code on his computer, Excel crashed and restarted every time he got to that line in the code. If anyone knows of any reason why that is happening, that would be helpful as well. We are both using Excel 2007.

Any help is appreciated!