PDA

View Full Version : Filter pivot item by date - type mismatch



mushkitoes
09-24-2018, 05:18 AM
I need help filtering pivot items with a date range. The items are dates in the format YYYY-MM-DD between 2014 and 2018. I would like for only the items of the past 12 months to be visible in the pivot table.

The code I came up with first checks all the items in the drop-down list of the pivot table. Then it should uncheck all items that are not within the 12 months range.

Problem: the code does not filter anything, therefore all items are still visible.

I used the watch window to see the value and type of the variables as the code goes through the For Each loop. It seems that I have a type mismatch issue with the pivot.visible = true/false method. Any ideas what could be the problem? I included a screenshot of the watch window.
Ex.:For the item 2014-01-01, i should get a pivot.visible= false of value "true" and of type "boolean", but i get type mismatch & Integer.

22917

Dim pivot As PivotItem
Dim currentMonth As Integer
Dim currentYear As Integer
currentMonth = Month(Date)
currentYear = Year(Date)

ActiveSheet.PivotTables("OEMI").RefreshTable
ActiveSheet.PivotTables("OEMI").PivotFields("Date sent to Coordinator").EnableMultiplePageItems = True

For Each pivot In ActiveSheet.PivotTables("OEMI").PivotFields("Date sent to Coordinator").PivotItems
If Not (Year(pivot) = currentYear And Month(pivot) <= currentMonth) Or _
(Year(pivot) = currentYear - 1 And Month(pivot) > currentMonth) Then
pivot.Visible = False

Else
'Do nothing and stay visible in the drop-down list
End If
Next pivot