Report Filter for Pivot not working with VBA (Excel 2010)

[trillium]

Hi all
I have a pivot table that I want to update via VBA based on dates user inputs on an input tab. (Want to use the Between function)

The Date field is in the Report Filter section (as I don't want it in the row or column area), but when I click on the dropdown arrow, I don't get any option to filter the date at all. I've double checked that the dates in my datasource are formatted for a pivot table, but that doesn't seem to be the issue.

When I use code I've found on this and other sites, it also doesn't work. When I run it, I get the following error at the "If Date DateValue(PI.Name) < StartDate Or DateValue(PI.Name) > EndDate Then..." line

Error Message: Unable to set the Visable Property of the Pivot Item Class

Any suggestions/ideas??

Sub Test()
    Const StartDate As Date = #1/15/2013#
    Const EndDate As Date = #2/13/2013#
    Dim PI As PivotItem
    For Each PI In ActiveSheet.PivotTables("PivotTable12").PivotFields("Date review Sent").PivotItems
        If DateValue(PI.Name) < StartDate Or DateValue(PI.Name) > EndDate Then
            PI.Visible = False
        Else
            PI.Visible = True
        End If
    Next PI
End Sub

 

[Jerry Sullivan]

Hi trillium,

There's a couple of reasons that you might be getting that error.

One possibility is that the code is trying to deselect the only report filter item that is currently selected.
Excel won't let you deselect all the items at the same time using VBA, even if your code will be selecting some other items before completing.

This code example addresses that by finding the first item within the date range and selecting that item first, before stepping through the entire list of items.

Public Function Filter_PivotField_by_Date_Range(pvtField As PivotField, _
        dtFrom As Date, dtTo As Date)
    Dim bTemp As Boolean, i As Long
    Dim dtTemp As Date, sItem1 As String

    
    On Error Resume Next

 
    With pvtField
        For i = 1 To .PivotItems.Count
            dtTemp = .PivotItems(i)
            bTemp = (dtTemp >= dtFrom) And _
                (dtTemp <= dtTo)
            If bTemp Then
                sItem1 = .PivotItems(i)
                Exit For
            End If
        Next i
        If sItem1 = "" Then
            MsgBox "No items are within the specified dates."
            Exit Function
        End If

        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual

       .Parent.ManualUpdate = True

 
        If .Orientation = xlPageField Then .EnableMultiplePageItems = True
        .PivotItems(sItem1).Visible = True
        For i = 1 To .PivotItems.Count
            dtTemp = .PivotItems(i)
            If .PivotItems(i).Visible <> _
                ((dtTemp >= dtFrom) And (dtTemp <= dtTo)) Then
                .PivotItems(i).Visible = Not .PivotItems(i).Visible
            End If
        Next i
    End With

    
    pvtField.Parent.ManualUpdate = False
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Function

Here's an example of the calling code. It assumes the From / To dates are in cells B1 and B2.
You could replace those with constants as you had in the OP, but my guess is that was just for your testing.

Sub Test_Filter_Date_Range()
    Dim dtFrom As Date, dtTo As Date
    Dim PT As PivotTable

 
    With ActiveSheet
        Set PT = .PivotTables("PivotTable12")
        dtFrom = .Range("B1").Text
        dtTo = .Range("B2").Text
    End With

    
    Call Filter_PivotField_by_Date_Range(PT.PivotFields("Date review Sent"), dtFrom, dtTo)
End Sub

Another possibilty is that you have blanks or other non-dates in your source data for that field.
If you have even a single blank, Excel treats all the values as Text, making it harder to do a serial date comparison.

 

[trillium]

Hi! That is brilliant! The code will at least now filter, however it is filtering in some strange ways that I can't figure out why.

I created a dummy table to practise on, dates from 01/01/2013 to 02/28/2013 and then 13/12/2013 to 31/12/2013. When I set the From To dates to: 01/15/2013 (B1) and 01/19/2013 (B2) it selects multiple dates, but more that what's needed. It choose 01/01/2013 to 02/28/2013.

Then I tested it with 02/28/2013 to 12/27/2013 and it took from 01/01/2013 to 27.12.2013.

So I manually set it back to (ALL) and ran again - same filter...???

Why is excel so finicky with dates and pivot tables???!!! (only a little bit frustrated... )

 

[Jerry Sullivan]

That code works for me in a mockup using the dates you tried, so it could be a difference in the dataset or regional date settings.
The date formatting was inconsistent in your last post. Were those just typos, or are some or all of you dates formated as "dd/mm/yyyy"?

Try using this version of the function which has some improved error handling. It will print debugging messages to the Immediate Window (Ctrl-G) in your VB Editor) that should help zero in on the problem.

Public Function Filter_PivotField_by_Date_Range(pvtField As PivotField, _
        dtFrom As Date, dtTo As Date)
    Dim bTemp As Boolean, i As Long
    Dim dtTemp As Date, sItem1 As String

 
    With pvtField
        Debug.Print "Finding first date meeting criteria..."
        For i = 1 To .PivotItems.Count
            On Error Resume Next
            dtTemp = .PivotItems(i)
            If Err.Number <> 0 Then
                Debug.Print .PivotItems(i) & " is not a valid date item"
                On Error GoTo 0
            Else
                bTemp = (dtTemp >= dtFrom) And _
                    (dtTemp <= dtTo)
                If bTemp Then
                    sItem1 = .PivotItems(i)
                    Exit For
                End If
            End If
        Next i
        On Error GoTo 0
        If sItem1 = "" Then
            MsgBox "No items are within the specified dates."
            Exit Function
        End If

        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual

       .Parent.ManualUpdate = True

        Debug.Print "Filtering to show items between dates..."
 
        If .Orientation = xlPageField Then .EnableMultiplePageItems = True
        .PivotItems(sItem1).Visible = True
        For i = 1 To .PivotItems.Count
            On Error Resume Next
            dtTemp = .PivotItems(i)
            If Err.Number <> 0 Then
                On Error GoTo 0
                Debug.Print .PivotItems(i) & " is not a valid date item"
                .PivotItems(i).Visible = False
            Else
                Debug.Print .PivotItems(i) & ": " _
                    & IIf((dtTemp >= dtFrom) And (dtTemp <= dtTo), "Show", "Hide")


                If .PivotItems(i).Visible <> _
                    ((dtTemp >= dtFrom) And (dtTemp <= dtTo)) Then
                        .PivotItems(i).Visible = Not .PivotItems(i).Visible
                End If
            End If
        Next i
        On Error GoTo 0
    End With


    
    pvtField.Parent.ManualUpdate = False
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Function

If that doesn't allow you to find the problem, please post anything unexpected in the debug.print output (don't post all 60+ lines).

 

[trillium]

Good morning!

Just quick - I had read that for pivot tables the dates need to be in the mm/dd/yyyy format for the pivot table to work. My error in transcribing the dates in the post above as I am used to writing them dd/mm/yyyy but they were input as mm/dd/yyyy (I just double checked to be sure). I had also set my regional setting to United States (instead of the Canada default which is dd/mm/yyyy) to be sure.

What are your settings? Did you use mm/dd/yyyy or dd/mm/yyyy?

I'm off to try your new code. Thank you so much.

 

[trillium]

Hi there

OK, firstly I double checked I had no spaces in the data column (find/replace). Then I checked the date format was correct by checking it was picking up the Month OK. All seems fine.

The code debugs after "Filtering to show items between dates..." at the << .PivotItems(sItem1).Visible = True>> step. When I look at sItems1 it has picked up the right date....??

The debug error message is: Unable to get the PivotItems property of the PivotField class.

        Debug.Print "Filtering to show items between dates..."
 
        If .Orientation = xlPageField Then .EnableMultiplePageItems = True
       .PivotItems(sItem1).Visible = True
       For i = 1 To .PivotItems.Count

I am also attaching my dummy workbook as well.

Very confused... not sure why it isn't working.... it all seems fine!

 

[trillium]

OK forgot I can't attach files to this forum... sorry.

 

[Jerry Sullivan]

I'll send you a PM with my email address.

 

[Jerry Sullivan]

Thanks for sending your file. It runs without error on my system, so it's probably related to the regional settings. (I'm not sure why that didn't work when you tried setting to U.S.)

Before I tinker with trying to get that to work with Regional Settings set for Canada dd/mm/yyyy, what date format do you want to have for your pivot items and the input cells (E1, E2)?

I understand you were using mm/dd/yyyy because you read that was needed. I don't think that's true- I think the format needs to match the regional setting when the date comparison is made. We should be able to make that work for whatever your first choice display formatting is.

 

[trillium]

Thanks so much for your help. Yes, I had changed my regional setting to US because I had read on previous posts it was needed for pivot filters to work (I had tried to figure it all out myself first

We are in Canada, so keeping it to Canada and using dd/mm/yyyy would ideal as that is how we usually enter our data here.

Cheers