Changing pivot table filter based on a drop down

2
votes

I have drop down which has a list of countries. I need the pivot table to be filtered based on the country selected on the drop down. I am using this code.

Sub PivotChange(ByVal Target As Range)

 If Not Application.Intersect(Target, Sheets("Summary").Range("D7")) Is Nothing Then
 Sheets("Data_4PivotChart").PivotTables("PivotTable7").PivotFields("Country"). _
    ClearAllFilters
Sheets("Data_4PivotChart").PivotTables("PivotTable7").PivotFields("Country").CurrentPage _
    = Sheets("Summary").Range("D7").Value
 End If

End Sub

But currently its not doing anything. Can anyone help me where should I add this code so that it will be triggered by the event.

Thanks

4
excelvbapivot-table
I read your comments from down below. Consider editing your post to make it more clear what you are trying to achieve, because everyone's first instinct is to tell you to use Excel's built-in pivot filter based on your statement at the top. – theMayer

4 Answers

4
votes

Although it might be too late for you, this might help someone else. The CurrentPage property is valid only for Page fields. Use the below code for the purpose:

Sub PivotChange(ByVal Target As Range)

 If Not Application.Intersect(Target, Sheets("Summary").Range("D7")) Is Nothing Then
 Sheets("Data_4PivotChart").PivotTables("PivotTable7").PivotFields("Country"). _
    ClearAllFilters
Sheets("Data_4PivotChart").PivotTables("PivotTable7").PivotFields("Country").PivotFilters.Add _
     Type:=xlCaptionEquals, Value1:=Sheets("Summary").Range("D7").Value
 End If

End Sub
0
votes

Are you familiar with the "Report Filter" feature of pivot tables? It is a drop down that will filter your pivot table. You don't need to have a custom drop down. You can read about it here:

http://office.microsoft.com/en-us/excel-help/use-a-report-filter-in-a-pivottable-or-pivotchart-report-HP010167854.aspx#BMdisplay_a_different_set_of_values_in_

Also Here:

http://www.contextures.com/Excel-Pivot-Table-Report-Filters.html

EDIT:

To control multiple pivot tables you can use a slicer:

http://blog.contextures.com/archives/2011/03/07/filter-multiple-pivot-tables-with-excel-2010-slicers/

EDIT AGAIN:

That is only if your pivot tables share the same data source.

Using single slicer to control two pivot tables with different data source in Excel

FOURTH EDIT:

You need to set up a worksheet change event on that cell with the drop down in it. The event will run your pivot table filter macro.

http://support.microsoft.com/kb/213612

0
votes

I had this same problem and I have an easier work-around without VBA macros. I had a source list of mobile devices sold per region per company per department per manager and various other field info on the mobile devices which I had to report on using multiple pivot tables like a Rubik's cube. The source list had various sales dates, but the the client only wanted the sales from last week, which means I had to manually select the week to be reported from the filter drop-down box for each pivot table which was painful and time-consuming before I could select refresh all.

Here is the solution: create a column in your source list and enter whatever criteria you wish to use - in my case I used a MAX function with a TRUE/FALSE evaluation criteria to find out if the record fell into the latest week. So you will have a list of TRUE/FALSE flags in this column. Then in your pivot tables filter select TRUE and your pivot tables will automatically filter to the true condition.

0
votes

Use of dynamic drop down will serve the purpose here. Here are simple example steps to give you an idea and you can apply this to your case:

  1. I created 4 tables: Vegetables, Fruits, Drinks, Places
  2. Each table has a list underneath (for ex. Vegetables: Carrot, Onion, Potato, Tomato; Fruits: Apple, Banana, Grape, Orange, Fig; Drinks: Coke, Fanta, Sprite, 7Up, Root Beer; Places: Park, Home, Work, Campus, College)
  3. Create name ranges for each table: Vegetables, Fruits, Drinks, Places
  4. In Cell F2 put a drop down by going to Data Validation - List - Source: reference the header cells for each category (=A2:D2)
  5. In Cell G2 put a drop down by going to Data Validation - List - Source: [here you need to use INDIRECT() FUNCTION]: type "=INDIRECT(F2).
  6. To test it out, in selection (cell F2) pick a value from the drop down. Based on the value you have picked the next sub-selection drop down should give you a list based on the main selection you had picked in cell F2.

Dynamic Drop down

Hope it helps, Thanks, Alisher Nizamov