Solved

Pivot table filter to All VBA

Posted on 2014-04-03
11
83 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

914 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

12 Experts available now in Live!

Get 1:1 Help Now