Pivot table filter to All VBA

Hello:
I am trying to set a pivot table filter to All through VBA. When I create a macro to see how it does it I get the below code.  Problem is, it doesn't work... No error or anything but the pivot table doesn't filter.....What gives? I am banging my head on the wall here.

Sheets("Chain By Day").PivotTables("PivotTable7").PageFields("Gallons").CurrentPage = _
        "(All)"
LVL 3
leeskelton83Asked:
Who is Participating?
 
andrewssd3Connect With a Mentor Commented:
This works for me:
    Dim p As PivotTable
    
    
    Set p = ActiveSheet.PivotTables(1)                   ' or whatever to get your pivot table
    
    With p
        .PivotFields("Gallons").ClearAllFilters
        .PivotFields("Gallons").CurrentPage = "(All)"
    End With
    

Open in new window

Is Gallons correct? This should be the label that appears to the left of Page filter drop down
0
 
FlysterCommented:
I can't seem to reproduce it. If that's what you got from the VBA recorder it should work. I would double-check the spelling of the tab and the name of the pivot table. Highlight the table, go to Pivottable tools, option tab. On the far left you should see the name of the table. One other thing you can try is:

    ActiveSheet.PivotTables("PivotTable7").PageFields("Gallons").CurrentPage = _
        "(All)"

It takes the tab name out of the equation, but it must be run from the same sheet as the pivot table.

Flyster
0
 
leeskelton83Author Commented:
Yep I already checked all that. Pivot table names, tabs etc. Even the macro I recorded doesn't work correctly. Tried activeshget too to no avail.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
leeskelton83Author Commented:
Yep it's correct , it's bizarre...
0
 
andrewssd3Commented:
Have you tried my code? - it's slightly different in that it uses the pivotfields property
0
 
leeskelton83Author Commented:
Well that does seem to work...but I used something similar before and it worked but only temporarily..Let me see if it holds...
0
 
leeskelton83Author Commented:
Seems to be working. Awesome. Thanks!
0
 
leeskelton83Author Commented:
Thank you andrewssd3.
0
 
andrewssd3Commented:
I think clearing the filters may also help.  Glad it's working for you.
0
 
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.