Filter all pivot table by same criteria

Solved/Closed

RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 - Feb 15, 2011 at 05:31 AM
RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 - Mar 1, 2011 at 07:14 AM

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

Filter all pivot table by same criteria
School time table software free download full version - Download - Organisation and teamwork
Pivot table date format mm/dd/yyyy - Guide
Html table size to fit window - Guide
Safe search filter - Guide
Filter unknown senders - Guide

4 responses

Answer 1 / 4

RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 120
Feb 21, 2011 at 01:56 AM

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

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 27, 2011 at 04:49 PM

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

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 17, 2011 at 08:28 AM

have you tried to record a macro to see if you change the pivot table filter what code is generated.

Answer 3 / 4

RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 120
Feb 18, 2011 at 03:03 AM

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

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 18, 2011 at 11:17 AM

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

RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 120
Feb 27, 2011 at 11:21 PM

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.

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 28, 2011 at 05:56 AM

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

RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 120
Feb 28, 2011 at 11:34 PM

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

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 1, 2011 at 06:04 AM

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

RWomanizer Posts 365 Registration date Monday February 7, 2011 Status Contributor Last seen September 30, 2013 120
Mar 1, 2011 at 07:14 AM

Thanks!

Done

Similar discussions

Excel - IF function with multiple words

If cell contains multiple text how to add other cell value?

Similar discussions

Subscribe To Our Newsletter!