Menu
Ask a question Report

Filter all pivot table by same criteria [Solved/Closed]

Posts
368
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
 - - Latest reply: RWomanizer
Posts
368
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
 - Mar 1, 2011 at 07:14 AM
Office Software Ask a question Report
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
See more 

4 replies

Best answer
Report Respond to RWomanizer
Posts
368
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
 193
1
Thank you
Hi, Riz!

I have upload the file as

http://www.speedyshare.com/files/26991190/TEST.xlsx

i have to filter all sheet by "Customer name"

and the criteria is in run sheets

Thanks!
Rahul

Say "Thank you" 1

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

CCM 3382 users have said thank you to us this month

Report
rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
 917 -
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
Report Respond to rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
 917
0
Thank you
have you tried to record a macro to see if you change the pivot table filter what code is generated.
Report Respond to RWomanizer
Posts
368
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
 193
0
Thank you
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
rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
 917 -
Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like http://www.speedyshare.com/ , 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
Report Respond to RWomanizer
Posts
368
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
 193
0
Thank you
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
rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
 917 -
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
RWomanizer
Posts
368
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
 193 -
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.

http://www.speedyshare.com/files/27143476/TEST.xlsm
Report
rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
 917 -
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
RWomanizer
Posts
368
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
 193 -
Thanks!

Done
Ask a question