Solved

Pivot table filter to All VBA

Posted on 2014-04-03
11
90 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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 47

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

726 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