2

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

braX
  • 11,506
  • 5
  • 20
  • 33
SAM244776
  • 1,375
  • 6
  • 18
  • 26
  • 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 Feb 01 '14 at 23:50

4 Answers4

4

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
xlViki
  • 43
  • 6
0

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

Community
  • 1
  • 1
Stepan1010
  • 3,136
  • 1
  • 16
  • 21
  • I think u did not get my question. The drop down list filters some other tables and charts also.consider this as a dash board where if u change one field everything should change with it. – SAM244776 Jul 25 '13 at 21:11
  • @user2472382 That aspect of your question I did not catch. To filter multiple pivot tables you can use a slicer - see my edit. Only if you have excel version 2010. – Stepan1010 Jul 25 '13 at 21:18
  • Thats again is true. Problem is I not only controlling pivot tables with this drop down but also some other charts which are not pivot charts...So I cannot use a slicer. I am trying write a VBA code which will be triggered when ever the drop down changes the selection. But i am not able to get it work. The code seems fine to me. – SAM244776 Jul 25 '13 at 21:21
  • @user2472382 Why are you using that PivotChange function? You need to use a worksheet change function to update the pivot table when that cell with the dropdown changes. Worksheet_Change(ByVal Target As Range) - See my fourth edit. – Stepan1010 Jul 25 '13 at 21:32
  • i am using worksheet_change ....now..but where to paste this code and how to activate? – SAM244776 Jul 25 '13 at 22:26
  • I tried with the example provided in the link...its not working – SAM244776 Jul 25 '13 at 22:36
  • getting error at this line Sheets("Data_4PivotChart").PivotTables("PivotTable7").PivotFields("Country").CurrentPage = Sheets("Summary").Range("C7").Value – SAM244776 Jul 25 '13 at 23:08
  • unable to set the currentpage property of the pivotfield class is the error – SAM244776 Jul 25 '13 at 23:15
  • @user2472382 You need to follow the directions better. You should right click on the sheet in the VBA editor and click "View Code". You can put code on a sheet, just like you would in a module. Once you put that code sample from Microsoft directly on the sheet then you can set the target to be the cell with your drop-down in it. Or you can just not set the target, although it will run the macro anytime any cell changes on the sheet then. – Stepan1010 Jul 26 '13 at 14:08
0

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.

Marc Andrew Johnston
  • 1
0

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

Taazar
  • 1,545
  • 18
  • 27
Alisher Nizamov
  • 1
  • 1