Solved

Pivot table filter to All VBA

Posted on 2014-04-03
11
85 Views
Last Modified: 2014-11-05
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)"
0
Comment
Question by:leeskelton83
11 Comments
 
LVL 22

Expert Comment

by:Flyster
ID: 39976984
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
 
LVL 3

Author Comment

by:leeskelton83
ID: 39978485
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
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 500 total points
ID: 39979895
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 3

Author Comment

by:leeskelton83
ID: 39980113
Yep it's correct , it's bizarre...
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 39980184
Have you tried my code? - it's slightly different in that it uses the pivotfields property
0
 
LVL 3

Author Comment

by:leeskelton83
ID: 39983059
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
 
LVL 3

Author Comment

by:leeskelton83
ID: 39983432
Seems to be working. Awesome. Thanks!
0
 
LVL 3

Author Comment

by:leeskelton83
ID: 39984063
Thank you andrewssd3.
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 39984061
I think clearing the filters may also help.  Glad it's working for you.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40423552
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question