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!
VBA Filtered Pivot Table Showing "Multiple Values Selected"; Only One Value Selected
Similar Threads
Threaded Mode
