VBA to filter pivot table from a cell reference in another sheet

Here is some "air code" that should work. It's as far as I can take it without a sample spreadsheet to work with.

First of all, you will probably want to set things up so that if you change the value in Cell A1 on Sheet 1, it will automatically run the macro to set the filter. Open the VB editor and double click on your sheet 1 and copy and paste this code in.

Please Login or Register  to view this content.

Then Select Insert -> Module and copy and paste this code in.

Please Login or Register  to view this content.

These macros could have been combined, but I like to keep things modular. Whenever you change the value in Cell A1, the first macro will run the SetFilter macro.

Make sure that your drop down list in Cell A1 on Sheet 1 has an accurate list to clients. Otherwise the pivot table will try to set the current setting to whatever is selected. The best way to make sure your list is always current is to make another pivot table that contains just the client names as the row value and then "overlay" it with a named dynamic range that is used for the data validation.

This article explains how to set up a named dynamic name: http://www.utteraccess.com/wiki/inde...Dynamic_Ranges.

You will want to get rid of the grand total row in the pivot table and you might want to consider changing the "Row Labels" header to "(All)" if indeed you want (All) as a choice in the dropdown list. Adjust the named range to include it or to just include the data in this pivot table as you wish.

Another option: if you have Excel 2010 or later is to use slicers. Put your cursor in the pivot table and select Insert -> Slicers from the ribbon. From there, the dialog boxes are fairly intuitive. You might like that display. No coding is required.

Slicers "cascade" which means that the choices in one slicer determine what is available for selection in another slicer. They can be used to control multiple pivot tables provided that the pivot tables are built from the same data source. Cosmetically, there are a lot of things you can do with them as well, such as have multiple columns.

This discussion would go a lot better if I had a concrete example to demonstrate on. Otherwise, I will be talking in the abstract.

There are a couple of ways around this issue. I am going to suggest avoiding the problem altogether.

Select the source data for the main pivot table and make another pivot table out of it. Save the pivot table to another sheet that you can hide later. In the row label put the item you want to filter on. Remove the totals row. This pivot table should now contain a unique list of the item on which you want to filter.

Overlay this with a named dynamic range such as Item_List. Go to Formulas -> Name Manager and add this name. In the refers to box at the bottom put =OFFSET($A$5,0,0,COUNTA(A:A)-1,1) - instead of typing $A$5 or A:A, select the cell and select the column when entering the formula. Intellisense will fill in the sheet name as well.

Also A5 might not be the cell you want. Use whatever cell in which the first item on the list appears.

What this formula says is:
Go to Cell A5
From there go down 0 rows and right 0 columns (so we are still in Cell A5)
Give me a range COUNTA(A:A) - 1 tall (the minus 1 is because we don't want to count the header) and 1 column wide.

It doesn't matter how many items are returned by the pivot table, this range will always point to the exact amount of data you need - and as mentioned above, this data will be a list of unique items on which you want to filter - sorted in whatever order you wish.

Use the named range as the list-type data validation for Cell A1. If the value is not in the data, it will not be on your dropdown list and you won't have the problem. Therefore, no changes in the code will be necessary.

Now, having said all of this. If you are using Excel 2010 or later, you also have available to you, slicers. Click in the pivot table and then Insert -> Slicers. The menu that pops up is quite intuitive. Slicers take seconds to set up, and you can always delete them. You can cut and paste them to another page. Investigate them and see if they work for you. Slicers look more like a listbox than a combobox and if this is OK by you, there are also a lot of cosmetic things you can do with them to make them look aesthetically pleasing.

One other advantage of slicers is that they can control multiple pivot tables provided the tables are all based on the same data set. So if you have several pivot tables that "slice" the data in different ways, slicers will set the filters on all of them simultaneously.

The picture is a lot clearer now .

I get by with the help of helper cells. I try to make Excel do as much of the "heavy lifting" as possible and then sew the pieces together like Dr. Frankenstein using VBA.

I created a sheet call Parameters (it's a better name than "The Sheet where I do my scratch calculations"). This sheet can be hidden.

I regurgitate the selection in Cell B1, just so I can see it for the purposes of QA.

Then in Column B, I use COUNTIF to see how many times the selection appears in the various tables. I decided that if the item did not appear, then the default filter would be "(All)" - this is sort of what Excel does with pivot tables where the filtered value doesn't exist: it reverts to all. The formulas in Column C do the work.

I used the values in Column C to set the filters.

As a result, the code hardly changes at all.

Please Login or Register  to view this content.

P.S. I just noticed. We don't need sh1 anymore.

The short answer is yes, but it's a bit more involved. To be really sure you do things right, you should check first to see if a filter is applied and then whether you need to clear it or not. I do have some "standard" code that does this. I'd have to dig it out. Then, as always, I'd have to record a macro to get the syntax for applying a filter.

It's easier doing it for a table since the table knows how big the data range is. I think you'd have to "probe" a data range to get its boundaries.

This is something I have to do for myself from time to time, so I think I will work up a generic subroutine to do it: something that you can pass a sheet name, a data range or table name, a column header (or an array containing column headers) and a value (or an array containing values). This may take some time, but I will post back here when I have it done.

Here is a piece of code that will set one value on one filter for a table. The test2 subroutine shows an example of how it is called from another subroutine.

Please Login or Register  to view this content.

Hi Dlfak,
Thanks for finding this, but I am having some trouble with it
How would I apply this to multiple tables? I basically want the same sort of process you applied to the pivot tables in my original request, allowing me to update entire sheets at once.

Do something like the following. Suppose the value you want to filter on is on Sheet "My Stuff" in cell B2.

Please Login or Register  to view this content.

The program has a change event on the overview sheet. The event is kicked off when Cell B1 is changed on the sheet. Cell B1 has a drop-down list data validation. The drop down list references a fixed range of cells (A4:A9).

The change event runs the macro SetFilter that is in Module 1. This macro looks at the Parameters Sheet (I call any sheet I do "scratch" calculations, Parameters). Cell B1 on the sheet merely regurgitates the selected value. Cells B3:B5 do a check to see how many times the selected client appears in the data. This is important because if you try to set a pivot table filter to a value that does not exist in the data, it will mess up the pivot table.

So if the selected value doesn't exist in the data, I set the pivot table value equal to (All).

This brings us to the set filter macro. It looks at the values in column C on the Parameters sheet and sets the filters on the sheets accordingly.

So, you need to update the client list on the Overview Sheet and the data validation as well.

The Parameters Sheet formulas reference the source data tables. Make sure your table names match the formulas.

Unless you change the sheet names, the program should work.