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

**kidwispa** · 04-05-2013, 11:18 AM

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!

**Portuga** · 04-06-2013, 07:49 AM

I´ve 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

**Portuga** · 04-07-2013, 09:30 AM

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.

**kidwispa** · 04-10-2013, 04:42 AM

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.

**Portuga** · 04-10-2013, 05:24 AM

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.

**watersev** · 04-10-2013, 05:49 AM

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.

**Portuga** · 04-10-2013, 06:15 AM

This bit is totally awesome:

Please Login or Register  to view this content.

**kidwispa** · 04-10-2013, 11:57 AM

Hi Watersev,

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

**tejal** · 06-24-2014, 03:35 PM

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.

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions