Solved

Pivot table filter to All VBA

Posted on 2014-04-03
11
82 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 45

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now