+ Reply to Thread
Results 1 to 9 of 9

Using VBA to select multiple values in a pivot table report filter based on a cell value

  1. #1
    Registered User
    Join Date
    04-04-2013
    Location
    Solihull, West Midlands
    MS-Off Ver
    2010
    Posts
    19

    Using VBA to select multiple values in a pivot table report filter based on a cell value

    Hi All,

    I posted the below thread to find out how to change a pivot table report filter based on a cell value, and found the solution I was looking for, however what I'm after now is a little more complex...

    http://www.excelforum.com/excel-prog...worksheet.html

    What I am looking to do is change a pivot table report filter to select multiple values based on a cell value in another worksheet. This is to total historical YTD by period. So as before I'm using cell D2 in sheet "Report" but this time it is PivotTable4 in sheet "T5".

    This is what I am trying to achieve...

    When 1 is selected in D2 Pivot Table filter selects 1.
    When 2 is selected in D2 Pivot Table filter selects 1+2
    When 3 is selected in D2 Pivot Table filter selects 1+2+3
    and so on... there are 13 periods in total however we are only in period 4 at present so there is no data for periods 5-13.

    Can anyone help?

    Thanks again!


  2. #2
    Forum Contributor Portuga's Avatar
    Join Date
    02-20-2004
    Location
    Portugal
    MS-Off Ver
    365
    Posts
    852

    Re: Using VBA to select multiple values in a pivot table report filter based on a cell val

    Ive been working on something like this recently and had the same issue.

    See if this helps.

    http://www.excelforum.com/excel-prog...down-list.html
    If you found the solution to your question. Mark the thread as "Solved"
    Thank everyone that helped you with a valid solution by clicking on their

    There is no such thing as a problem, only a temporary lack of a solution

  3. #3
    Forum Contributor Portuga's Avatar
    Join Date
    02-20-2004
    Location
    Portugal
    MS-Off Ver
    365
    Posts
    852

    Re: Using VBA to select multiple values in a pivot table report filter based on a cell val

    I resolved this issue not long for a project I was doing.

    It took a bit of time to get it working the way I wanted, so I guess this is a good place to put the detailed solution I found to work for me.

    This method will get a macro to select specific items from a pivot table filter. It will select only the items specified in the macro, making all others invisible.
    In case none of the field items is found, the pivot will display nothing.

    It will ignore errors if 1 or more specified items are not available on the current dataset, diaplaying the info only for the ones that are.

    Please Login or Register  to view this content.
    To get the macro to be activated by a cell value, you must insert something like this into the page code (Not in the module)
    Right click on page and select "view Code). This will activate the macro above if the cell value in B5 is changed to 3

    Please Login or Register  to view this content.
    Last edited by Portuga; 04-07-2013 at 09:47 AM.

  4. #4
    Registered User
    Join Date
    04-04-2013
    Location
    Solihull, West Midlands
    MS-Off Ver
    2010
    Posts
    19

    Re: Using VBA to select multiple values in a pivot table report filter based on a cell val

    Hi Portuga,

    Thanks for your replies... I've tried using the code you suggested and made the alterations you suggest but here is what happens...

    I set the reporting filter to all at the beginning, then try selecting the number 3 - this does then change the report filter to periods 1-3 as I wanted. If I then select either 1,2 or 4, it still stays on periods 1-3? Is there something else I need to add?

    Here is exactly the code I have put in...

    In the module:

    Please Login or Register  to view this content.
    In sheet "Report":

    Please Login or Register  to view this content.
    Thanks again for any help.


  5. #5
    Forum Contributor Portuga's Avatar
    Join Date
    02-20-2004
    Location
    Portugal
    MS-Off Ver
    365
    Posts
    852

    Re: Using VBA to select multiple values in a pivot table report filter based on a cell val

    1 working macro down, 12 to go...

    And in the page you will need something like this:

    Please Login or Register  to view this content.

  6. #6
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2003
    Posts
    4,168

    Re: Using VBA to select multiple values in a pivot table report filter based on a cell val

    hi kidwispa, option, change value of Report sheet D2 cell and check result on T5 sheet. If nothing happens on changing D2, run code "Enable_Events" first and try again.
    Attached Files Attached Files

  7. #7
    Forum Contributor Portuga's Avatar
    Join Date
    02-20-2004
    Location
    Portugal
    MS-Off Ver
    365
    Posts
    852

    Re: Using VBA to select multiple values in a pivot table report filter based on a cell val

    This bit is totally awesome:

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    04-04-2013
    Location
    Solihull, West Midlands
    MS-Off Ver
    2010
    Posts
    19

    Re: Using VBA to select multiple values in a pivot table report filter based on a cell val

    Hi Watersev,

    Thanks for your suggestion - it works great (thanks again to Portuga for your help as well!!!!)


  9. #9
    Registered User
    Join Date
    05-20-2014
    Posts
    9

    Re: Using VBA to select multiple values in a pivot table report filter based on a cell val

    Hi, the first code Portuga posted is amazing, thank you. Just had one question: can this code be altered to work on two pivot tables. N.B. I have 15 tables on the same sheet, just need the code to work on 2 specific tables.

    Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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