Solved

Pivot table filter to All VBA

Posted on 2014-04-03
11
84 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

773 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