VBA to Change Filter in Multiple Pivot Tables based on A Cell Value

This Excel VBA Tutorial explains VBA Code to Apply Filter in Multiple Pivot Tables. We can use drop down list from a Cell as Filter for multiple pivot tables. VBA Code will loop through all pivot tables on the sheet and change value in filter as per selected value.
Website :- WWW.EXCELDESTINATION.COM

[@Henry4YWH]

Out of all the sites I checked, this is the only one that worked. Thanks!

[@sirajkhan6945]

Works for me, thank you so much, with loads of love.

[@CesarLlanosBaron]

Awesome!!! thank you very much, Im going to try it right now... I have 14 Pivot Tables in one Sheet!!!!

[@ExcelDestination]

Have fun!

[@sarano8405]

Thanks so much, great video. Is there a way to set all Pivot tables to refresh immediately (without having to click that Refresh button) when a cell value at dropdown list changes?

[@joramandrew3805]

This is great and very helpful however does this code work for pivot tables connected using data model.?

[@adrianobrito8007]

Hi! tks for the explanation.. how can I change the code to include 2 more cells with dropdown for the filter?

[@ExcelDestination]

that would require some additional code

[@adolfmontaner9937]

@@ExcelDestination You can try this one I also added two for dropdown on mine and here's the code.
Sub Btn_Filter()
Dim a As String
Dim b As String
Dim c As String
Dim pt As PivotTable
Dim pt2 As PivotTable
Dim pt3 As PivotTable
ThisWorkbook.Worksheets("Ledger").Activate
a = Worksheets("Ledger").Cells(3, 1).Value
For Each pt In ActiveSheet.PivotTables
With pt.PivotFields("WBS ID")
.ClearAllFilters
.CurrentPage = a

End With
Next
b = Worksheets("Ledger").Cells(3, 2).Value
For Each pt2 In ActiveSheet.PivotTables
With pt2.PivotFields("Accounting year")
.ClearAllFilters
.CurrentPage = b

End With
Next
c = Worksheets("Ledger").Cells(3, 3).Value
For Each pt3 In ActiveSheet.PivotTables
With pt3.PivotFields("Accounting month")
.ClearAllFilters
.CurrentPage = c

End With
Next
End Sub

[@emilytran84]

How this work with multiple value instead of one cell ? Thank you.

[@kissxk]

Hi, thanks for this. is the command any different for a powerpivot table where the source is the datamodel? thanks

[@robhermany]

You have great videos. Thank you for all of your help! Similar to your form video where you update the source data with a "Search and Display button", how would you update multiple pivot tables and slicers/upon entering in an opp_ID into a textbox form? In other words, instead of changing a filter in multiple pivot tables based on a cell value, it would be to change filter in multiple pivot tables based on a VBA form input / text box?

[@indianmonster1445]

wow........finaly i got it this trick .....

[@ahmadaltayeb1983]

Sir, this code give me run time error 1004, and mark on line (.CurrentPage=a), i use office 2019 and 2021, please advice

[@joshuarivera3023]

Same here

[@joshuarivera3023]

Do you have resoluton on this?

[@ahmadaltayeb1983]

@@joshuarivera3023 No Sir

[@firozbaigmogal519]

good morning sir
Awesome!!! thank you very much
how to create the button pls tell me

[@jaimecastellanos87]

This was amazing! But Im having issues on the .currentpage=a / Run-time error '1004': Unable to set the CurrentPage property of the PivotField Class. How could I fix it sir? Thank you

[@dallinperkes8956]

The code from the video should work as long as you have multiple pivot tables, that are on the same sheet and have variables with the same names..... You would run into the error 1004 if you were trying to run this code on a singular pivot table.
If this is the case, and you only have one pivot table, you can use the following code to do this same function......
Sub FilterPageValue()
Dim pvFld As PivotField
Dim strFilter As String
Set pvFld = ActiveSheet.PivotTables("PivotTableName").PivotFields("VariableName")
strFilter = ActiveWorkbook.Sheets("SheetName").Range("Cell(ex. B4)").Value
pvFld.CurrentPage = strFilter
End Sub

[@ganesha243]

May i know, how to work on different worksheet pivot tables?

[@technicalknowledge9128]

sir one question i have cell value name like 50 and i want to add name one by one add to value field
how to use please give me solution all name have column base and i want to name have a list and add to value by loop

[@euridiceferreira569]

Good morning Sir. This a very important example. How would you modify the code if the pivot tables were in different sheets within the same workbook? Appreciate your response. Thank you.

[@azmipangesti6470]

i have the same question with you. have you know the answer? if you know the answer i hope you can share to me. thank you

[@heng9876]

May i know How do we change the filter based on just another cell value instead of clicking the button?

[@foradacaixinhapodcast]

You can use On Cell Value Change

[@armandorengifo8338]

Thanks a lot man!!

[@ExcelDestination]

Welcome!

[@ganesha243]

How to protect worksheet while using dropdown list for pivot tables?

[@Nainagrh0712]

my query, what to do if mutiple selection of cities, written in a list like, "Pune", "Delhi", "abc" then? it is not working in .CurrentPage = array("city1","city2"), lplease correct

[@sahilkumar-iw2or]

did you get it to work?

[@cSR-71]

if your data source is the same ... you can use a slicer. This method will not work for multiple selections.

[@rushikeshkulkarni6075]

I have one pivot Table so then how i can use this code ?
Bcoz the code is " for each pt in Activesheet pivot tables"

[@dallinperkes8956]

Sub FilterPageValue()
Dim pvFld As PivotField
Dim strFilter As String
Set pvFld = ActiveSheet.PivotTables("PivotTableName").PivotFields("VariableName")
strFilter = ActiveWorkbook.Sheets("SheetName").Range("Cell(ex. B4)").Value
pvFld.CurrentPage = strFilter
End Sub

[@luirm6946]

Hi boss, is there any way you can share the code with multiple pivot fields selected

[@danirasys]

I tried this, but I get an error on '.CurrentPage=a' Any idea why?

[@ExcelDestination]

without looking at complete code, I can't say anything. You can re check your sheet name and code.

[@danirasys]

Sorry, I didn't realize I left that out! I will check again and share the full data when I get home from work.^^

[@danirasys]

Here is what I have in my VBA:
Public Sub UpdatePivotFields()
Dim kt As String
Dim ft As String
Dim cl As String
Dim ti As String
Dim pt As PivotTable
ThisWorkbook.Worksheets("Pivot Table").Activate
kt = Worksheets("Pivot Table").Cells(2, 4).Value
ft = Worksheets("Pivot Table").Cells(2, 5).Value
cl = Worksheets("Pivot Table").Cells(2, 6).Value
ti = Worksheets("Pivot Table").Cells(2, 7).Value
For Each pt In ActiveSheet.PivotTables
With pt.PivotFields("Korean Teacher")
.ClearAllFilters
.CurrentPage = kt
End With
Next
For Each pt In ActiveSheet.PivotTables
With pt.PivotFields("Foreign Teacher")
.ClearAllFilters
.CurrentPage = ft
End With
Next
For Each pt In ActiveSheet.PivotTables
With pt.PivotFields("Class")
.ClearAllFilters
.CurrentPage = cl
End With
Next
For Each pt In ActiveSheet.PivotTables
With pt.PivotFields("Time")
.ClearAllFilters
.CurrentPage = ti
End With
Next
End Sub

[@danirasys]

I appreciate your kindness to look at it. Thank you very much!

[@stevemaxwell197]

Did you ever figure this out? I got this to work a month ago but when got back into today I get error in same place.

[@hallinhko]

Brilliant thank you very much

[@ExcelDestination]

You are very welcome

[@sahilkumar-iw2or]

@Excel Destination : Sir , if in the same case we have multiple cities in the data, like 1 row is delhi, Mumbai Other row is Delhi, other is Delhi,Pune. For this data how can we use the VBA? (like in excel we use logic in pivot ''CONTAINS'' Delhi)

[@victoriardgs7397]

Hi there, I have tried this but it only works on sheets where my pivot tables are next to each other. Any way to slightly adapt this for pivot tables underneath each other ? Thanks !

[@ankitshah6892]

Hey! Did you get the solution for the problem mentioned? If yes plese share it me..

[@jeronimoguardado7699]

Hi!! Awesome, but how can I clear all filters back??

[@ExcelDestination]

same code you can use to remove filter, try this and let me know if it works

[@jeronimoguardado7699]

@@ExcelDestination Hi!!, but what do you mean by using the same? do i need to copy and paste all the same code and add a diferente button? Im not sure how to do it, because in this code Im telling excel to filter whenever I choose a City but what should I select to unfilter it?

[@DCHAWADA07]

Sir awsome I was searching this from so many days, actually my office uses excel 2007 which doesn't have slicers so I need multiples selection to b done to be updated in pivot chart for dashboard , same as slicers work selecting multiples fields, pls help how do I do that ? 😞

[@ExcelDestination]

let me research for that

[@DCHAWADA07]

@@ExcelDestination thank you so much sir, appreciate your response

[@ganesha243]

How to add dependent dropdown list ?

[@alparkson5494]

This did not work for me. The error I received was "unable to get the pivotfields property of the pivottable class" for the field I was trying to filter on.

[@BharatKumar-fh7ei]

It's very good.
Instead of Pivot table, can we work in excel sheet?

[@ExcelDestination]

yes why not!

[@drewwingert831]

How would this work in Google Sheets? Is it possible?

[@ExcelDestination]

it is very much possible in google sheets

[@drewwingert831]

@@ExcelDestination Thanks! Where is the VBA option in Google Sheets?

[@drewwingert831]

I think I did some of it
I recorded a Macro simialr to yours, but I am unsure how to create the button...any other instructions to do this in Google Sheets?

[@drewwingert831]

@@ExcelDestination Any feedback on how?

[@Just-My-Opinion]

is it posible to have 2 drop down menus and filter 2 items instead of just one?

[@manishvarshney601]

Same question, is selecting 2 items in this VBA macro possible?

[@123456789mumbai]

Hello everyone, need your help if anyone can share some input
I need to select 10 items in fieldname which are available in sheet 1 using vba. What code can be used?

[@_bellecullen]

Thank you!!!!!

[@ExcelDestination]

You're welcome!

[@mimiten7626]

How to change multiple table with different city values?

[@EXCELTrainingg]

Thank you so much

[@EXCELTrainingg]

Just Worthy

[@ibadkhan481]

It’s giving me error in “.Current Page= a” option please help

[@ExcelDestination]

not sure, you can share your file at [e-mail]

[@louisurban2342]

@@ExcelDestination Did this ever get resolved? I'm having the same issue

[@djrobincooper]

Superb thank you

[@indianmonster1445]

sir please How to use formula in pivot tables ..........i don't understand...... please make this ......

[@ExcelDestination]

you can use calculated field option in pivot table. it is simple to use.

[@Raj-ph1py]

Excellent tutorial

[@ExcelDestination]

Thank you! Cheers!

[@obuleshkumar4866]

vba code to select filter in 1 pivot table...Please

[@zadirarivera3271]

This is a great video. I am trying to do it but I am getting Error 1004 Unable to set the CurrentPage property of the PivotField class. I haven't been able to find the error. This is my code, I would appreciate your help. Thanks
Private Sub CommandButton1_Click()
Dim a As String
Dim pt As PivotTable
ThisWorkbook.Worksheets("Reconciliation").Activate
a = Worksheets("Reconciliation").Cells(4, 11).Value
For Each pt In ActiveSheet.PivotTables
With pt.PivotFields("Order")
.ClearAllFilters
.CurrentPage = a
End With
Next
End Sub

[@ExcelDestination]

you can share your code at [e-mail], I will check and respond

[@sanjaysamant4555]

Excel Destination I’m getting same error and I have 12 tables...can you help???

[@adolfmontaner9937]

New subscriber here, btw great tutorial.
I was trying to add another dropdown and here is the code but I am getting the error below:
Run-time error '1004':
Application-defined or object-defined error
----------------------------Code: --------------------------------------
Private Sub Btn_FilterPivot_Click()
Dim a As String
Dim b As Double
Dim pt As PivotTable
ThisWorkbook.Worksheets("Ledger").Activate
a = Worksheets("Ledger").Cells(19, 2).Value
b = Worksheets("Ledger").Cells(19, 5).Value
For Each pt In ActiveSheet.PivotTables
With pt.PivotFields("WBS ID")
.CurrentPage = a
'.ClearAllFilters'
End With
Next
For Each pt In ActiveSheet.PivotTables
With pt.PivotFields("Accounting Month")
.CurrentPage = b
'.ClearAllFilters'
End With
Next
End Sub
----------------------------Code: --------------------------------------

[@adolfmontaner9937]

I got it, this one works for me:
Sub Btn_Filter()
Dim a As String
Dim b As String
Dim c As String
Dim pt As PivotTable
Dim pt2 As PivotTable
Dim pt3 As PivotTable
ThisWorkbook.Worksheets("Ledger").Activate
a = Worksheets("Ledger").Cells(3, 1).Value
For Each pt In ActiveSheet.PivotTables
With pt.PivotFields("WBS ID")
.ClearAllFilters
.CurrentPage = a

End With
Next
b = Worksheets("Ledger").Cells(3, 2).Value
For Each pt2 In ActiveSheet.PivotTables
With pt2.PivotFields("Accounting year")
.ClearAllFilters
.CurrentPage = b

End With
Next
c = Worksheets("Ledger").Cells(3, 3).Value
For Each pt3 In ActiveSheet.PivotTables
With pt3.PivotFields("Accounting month")
.ClearAllFilters
.CurrentPage = c

End With
Next
End Sub

[@lahaluke]

Can anyone get this to work? Always gets stuck on the .currentpage=a
Private Sub CommandButton1_Click()
Dim a As String
Dim pt As PivotTable
ThisWorkbook.Worksheets("Analysis").Activate
a = Worksheets("Analysis").Cells(1, 4).Value
For Each pt In ActiveSheet.PivotTables
With pt.PivotFields("City")
.ClearAllFilters
.CurrentPage = a
End With

Next
End Sub

[@lahaluke]

It has to be a filter on the pivot table. It CANNOT be a row or column. Thanks.

[@ExcelDestination]

ok

[@alexy6093]

Wouldn't be easier if you just use a slicer?

[@cSR-71]

Only if the pivot tables are using the same data source. I can't because my pivot tables are from different data sources, though all have common 'cities'.
Also need to make multiple selections, like a slicer can do.

[@haryprasetyo4495]

Hello Sir..i tried few times but seem not work and stop at .ClearAllFilters.. can you help..here with my script following your script
Sub Button2_Click()
Dim a As String
Dim pt As PivotTable
ThisWorkbook.Worksheets("Summary").Activate
a = Worksheets("Summary").Cells(1, 8).Value
For Each pt In ActiveSheet.PivotTables
With pt.PivotFields("Time - Month")
.ClearAllFilters
.CurrentPage = a
End With

Next
End Sub

[@raghavendrabhat3668]

Sub Button1_Click()
Dim a As String
Dim pt As PivotTable
ThisWorkbook.Worksheets("PIVOTS").Activate
a = Worksheets("PIVOTS").Cells(2, 20).Value
For Each pt In ActiveSheet.PivotTables
With pt.PivotFields("Pole")
.ClearAllFilters
.CurrentPage = a
End With
Next
End Sub
Gettting error on line "With pt.pivot........

[@udeychowdhury2529]

Thanks for this

[@ExcelDestination]

My pleasure!