Previous Topic Next Topic

Filter all pivot table by same criteria

Solved/Closed
Report
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
 -
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
 -
Hello,



I am using excel 2007.

I have a data on sheet1:

I had make 4 pivot tables from that data in four different sheets:

Now I have customer name in report filter of all pivot table.

now I have to view all four pivot by filtering the same in customer name. I have to do this one by one.

Can their is any way that I can do this by filter in single sheet and all pivot filter itself.

Thanks!
Rahul

4 replies

Answer 1 / 4
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
 120
Hi, Riz!

I have upload the file as

https://authentification.site/files/26991190/TEST.xlsx

i have to filter all sheet by "Customer name"

and the criteria is in run sheets

Thanks!
Rahul
1
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
You can try some thing like this

on your main filter sheet ( in sample that would be "RUN") add this code 

Private Sub Worksheet_Change(ByVal Target As Range)
   
   Dim sValue        As Variant
   Dim sField        As String
   
   sField = "CUSTOMER NAME"
   sValue = Target.Value
   
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   Call doClearFilter("Table1", "PivotTable1", sField)
   Call doPivotFilter("Table1", "PivotTable1", sField, sValue)
   
   Call doClearFilter("Locations", "PivotTable8", sField)
   Call doPivotFilter("Locations", "PivotTable8", sField, sValue)
   
   Call doClearFilter("Category", "PivotTable10", sField)
   Call doPivotFilter("Category", "PivotTable10", sField, sValue)
   Application.ScreenUpdating = True
   Application.Calculation = xlCalculationManual
   Application.CalculateFull
   
End Sub




add a new module and add this code 
Sub doClearFilter(sSheetName As String, sPivotTableName As String, sPivotField As String)
   Sheets(sSheetName).PivotTables(sPivotTableName).PivotFields(sPivotField).ClearAllFilters
End Sub

Sub doPivotFilter(sSheetName As String, sPivotTableName As String, sFieldName As String, sValue As Variant)

   Dim vItem         As Variant
   
   For Each vItem In Sheets(sSheetName).PivotTables(sPivotTableName).PivotFields(sFieldName).PivotItems
     vItem.Visible = (vItem = sValue)
   Next

End Sub
Answer 2 / 4
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
have you tried to record a macro to see if you change the pivot table filter what code is generated.
Answer 3 / 4
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
 120
hi Riz!


i got code if the filter value is in report filter. 
    Sheets("Table1-value").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("CUSTOMER NAME"). _
        CurrentPage = "ACC"


its work because here I have only single selection. all four tables are filter by a single word.

but I tried it but it not work if the "CUSTOMER NAME" is in Row Label
when recording the macro I got 
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("CUSTOMER NAME")
        .PivotItems("ACC.").Visible = True
        .PivotItems("XYZ.").Visible = False

its means first I have to false the critiria which is filter than apply my filter.

i think its not time saving. :(

so can you please help me out.


Thanks!
Rahul
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc
AND post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too.


Note: your data need not be to be real data but a good representative of how data looks like
Answer 4 / 4
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
 120
Hi Riz,

It shows error "object required" by highliting the below code 
sValue = Target.Value

if I replace this with 
sValue = Range("a4").Value

and then tray to execute the code.

it show the error as "Unable to set the visible property of the pivotitem class and highlight the below code 
For Each vItem In Sheets(sSheetName).PivotTables(sPivotTableName).PivotFields(sFieldName).PivotItems
     vItem.Visible = (vItem = sValue)

in doPivotFilter() function.
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
I ran this code on the sample file that you provided. It worked on that. May be if you could upload a file that you are using to test that gives you error, one can tell what could be the issue
Report
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
 120
I am also running the code in sample file it deos not ran properly.

so I am here upload the sample file with macro for your reference.

https://authentification.site/files/27143476/TEST.xlsm
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
The code that I gave you was supposed to work with this sub (as in the code that I gave you).
Private Sub Worksheet_Change(ByVal Target As Range)

you have converted it into a button call. it is failing to get value of "sValue = Target.Value". fix that issue and you should be all set
Report
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
 120
Thanks!

Done
Ask a question