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]

Well, that's a tricky problem.

It's definitely related to the regional settings as I can replicate the error you are getting by setting my regional settings to English (Canada), and it works fine when I switch back to English (US).

I'm not sure why it didn't work for you when you switched to US; however I notice that your Data Source is referencing entire columns A:C. This adds to the challenge by mixing dates with blanks.

This article has some good discussion on the topic (it might be one you read previously).

Excel 2007 accessing PivotItem.Visible gives error if the field item value is a DATE

I haven't had a chance to look at the referenced articles, but it looks like the posters found some work arounds involving setting the Number Format directly to the field.

 

[trillium]

THANK YOU SO MUCH!! I had used columns because each month new data is being added, so thought it was easiest to ensure I wasn't going to miss any data. I will go and read the article you posted. If I can ever get it to work, I will let you know!! Now that I'm in this problem, I am bound and determined to get this to work!

 

[esbkaz]

I was trying to do the same thing as Trillium and found your backwards and forwards discussion and code very helpful.
BUT after running the code, I looked in the Immediate window and got a whole lot of lines like this (see below) - which is fantastic because I wanted to only show dates in April so I thought wonderful it works. The problem is that this didn't seem to flow thru to the PivotTable itself because it didn't change. Any ideas? (BTW I'm in Excel 2013)

4/26/2015: Show
4/29/2015: Show
5/3/2015: Hide
5/8/2015: Hide

 

[Jerry Sullivan]

Hi esbkaz, Handling dates in PivotTables when the user's regional settings are not set to US can be vexing.

If be glad to take a look at your example workbook (with any sensitive data removed) to try to sort out the cause. Just send me a PM with your email address if you'd like to do that.

 

[Jerry Sullivan]

Hi esbkaz,

I reviewed the file that you sent me. By setting my Regional Settings to match yours English (Australia), I was able to replicate the problem you are having.

The code in this thread was intended for use with Report Filters. Since you are wanting to filter a RowField that contains dates, a much better approach is to use the PivotFilters object. This thread shows example code.

http://www.mrexcel.com/forum/excel-...e-ranges-outside-pivot-table.html#post3549710

Please let me know if that works for you.

 

[esbkaz]

Thanks so much this really did the trick.
The Pivot Charts that are hanging off the PT's are now not as pretty as they were when I had a slicer to just pick an entire year but that is what the client asked for.

Thanks for your help.