How to filter pivot items with date range with Excel VBA

Question

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.

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

EDIT***************** 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? Watch Window

Snippet of the data

No, the pivot items are in the date format. Year(date) and Year(pivot) return a integer. — Mushkitoes
The pivot table takes its source data from another Workbook. I already verified that the format of the cells in the source data are in the date format YYYY-MM-DD. — Mushkitoes

Mushkitoes Mushkitoes · Accepted Answer · 2018-09-25T13:55:16

I found a solution to display all items of the last 12 months. I've used the excel numerical value of dates and used this to filter the pivot items.

'Define the pivot items
Dim pivot As PivotItem

'Refresh the pivot table
ActiveSheet.PivotTables("OEMI").RefreshTable 'VBA will now use the current pivot item rather than the previously cached _
                                              pivot items


'Add filters for "Date sent to Coordinator"
ActiveSheet.PivotTables("OEMI").PivotFields("Date sent to Coordinator").EnableMultiplePageItems = True 'Select Multiple Items

'Define the date variables
Dim CurrentDate As Long 'Convert the current date to excel numerical date
Dim LastYear As Long
LastYear = CurrentDate - 365 'Numerical value of the date 12 months ago

For Each pivot In ActiveSheet.PivotTables("OEMI").PivotFields("Date sent to Coordinator").PivotItems

    If CLng(pivot) >= LastYear Then
        pivot.Visible = True
    Else
        pivot.Visible = False
    End If

Next pivot

This solution works perfectly for the application of the pivot table. I didn't solve the problem per se, but I get what I need.

Thanks to those who took the time to read the post and try to help.

Have a great day!

How to filter pivot items with date range with Excel VBA

2 Answers