+ Reply to Thread
Results 1 to 3 of 3

Pivot Table select all Pivot Items not working as intended

  1. #1
    Forum Contributor
    Join Date
    07-24-2008
    Location
    Athome
    MS-Off Ver
    Excel 2013
    Posts
    127

    Pivot Table select all Pivot Items not working as intended

    Hi all

    I'm still at intermediate level when it comes to VBA, spend a lot of time lurking on here and adapting code as needed.

    I have a pivottable and am using VBA to create different views.
    One of the Fields is "Forecast Flag" which has values of either "Yes" or "No". The pivot dropdown, however, shows "Yes, No, (blank)".

    I can successfully manipulate the Pt to filter the Pf to "No".
    However, after copying and pasting this info to a new sheet, I then want to change the Forecast Flag to "Yes" only.

    When changing the filter back from "No" to "Yes", it selects BOTH "Yes" and "No".
    To do this, I run the same code but "in the inverse form".
    When I duplicate the code in "equal and opposite form", it then gives me the desired result - see code below to understand what I mean by "inverse form" and "equal and opposite form"

    Initially, this code is used to filter on "No"
    Please Login or Register  to view this content.

    Later on, I am using this code to swith to "Yes"
    Please Login or Register  to view this content.
    Weird.. can anybody spot the problem here or otherwise advise how to do this more efficiently?
    I'm going to be creating about 5 or 6 different views and would hate to have duplicate/inefficient code in my module.

    Thanks.
    Last edited by rasonline; 09-19-2013 at 06:12 PM.

  2. #2
    Forum Contributor
    Join Date
    07-24-2008
    Location
    Athome
    MS-Off Ver
    Excel 2013
    Posts
    127

    Re: Pivot Table select all Pivot Items not working as intended

    No responses yet, so I'm guessing my description is unclear.
    In summary, I have a pivottable field with options Yes,No,(blank).

    I use code to filter on "Yes". Works fine.
    I then use the same code to filter on "No", but this doesn't work. It displays "Yes" and "No".
    I then have to add additional code to filter out "Yes".

  3. #3
    Forum Contributor
    Join Date
    07-24-2008
    Location
    Athome
    MS-Off Ver
    Excel 2013
    Posts
    127

    Re: Pivot Table select all Pivot Items not working as intended

    In the end, the solution was quite simple.
    First filter on one item eg. "yes"
    Then set all items to visible
    Then filter on the other item eg. "no"

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Pivot table- Select only items having two different variables
    By Excel Dumbo in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 05-13-2013, 07:21 PM
  2. Select all items in pivot table in VBA
    By JohnSeito in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-02-2013, 04:42 AM
  3. Pivot Table-Select Pivot Items
    By jomili in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-28-2011, 01:20 PM
  4. Select and unselect items in a filter in pivot table
    By TPFKAS in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-10-2010, 09:39 AM
  5. [SOLVED] Macro - Pivot Table - Select Items
    By S Sainsbury in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-18-2005, 11:30 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1