Filter multiple PivotTables based on Cell Values

[palcolon]

I have 3 separate PT on a worksheet. Each PT's datasource is linked to a separate tab. I would like to control the filter "employee filter" by typing/selecting the employee's name on cell A3 (whicheve cell).

I've looked around in the forums and found a few threads with almost the same request, but none that seem to work. Taking bits and pieves from each thread I was able to write the following:

This is what I have so far and it "kind of works" (i'm new at VBA):

Sub filter_all_tables()
'
' filter_all_tables Macro
'
'
Range("C3").Select
Selection.Copy
ActiveSheet.PivotTables("PivotTable3").PivotFields("Collector filter"). _
CurrentPage = "(All)"
On Error Resume Next
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Collector filter"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("Collector filter"). _
CurrentPage = "c3"
ActiveSheet.PivotTables("PivotTable1").PivotFields("Collector filter"). _
CurrentPage = Range("c3").Value
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Collector filter"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields("Collector filter"). _
CurrentPage = Range("c3").Value
End With
ActiveSheet.PivotTables("PivotTable3").PivotFields("Collector filter"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable3").PivotFields("Collector filter"). _
CurrentPage = Range("c3").Value
Range("A5").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range("C3").Select

End Sub


The problem that i am having is, whever i select an employee name that is not lited on any of the tables, the tables then lists everyone. I would like the table to be blank/display nothing if the employee is not found.

I hope i'm explaining this correctly. Thanks for your time!

 

[jorismoerings]

Hi and welcome to MrExcel,

Your problem of showing all employee names if you select a non-listed name is caused by the "ClearAllFilters" statement in your code. To prevent selecting a non listed name you have to create a list of unique values from the 3 data sources. Next array-formula will create a list of unique values from 3 sources where you have to change the list1, list2 en list3 references to your data sources.

=IFERROR(IFERROR(INDEX(List1,MATCH(0,IF(ISBLANK(List1),1,COUNTIF($D$1:D1,List1)),0)),IFERROR(INDEX(List2,MATCH(0,IF(ISBLANK(List2),1,COUNTIF($D$1:D1,List2)),0)),INDEX(list3;MATCH(0,IF(ISBLANK(list3),1,COUNTIF($D$1:D1,list3)),0)))),"")

Use the unique items list as a data validation list for your selection cell.
Than use a macro which will change the filter cell on all pivot tables to the value you've selected with the data validation.
The macro for updating the PT could be something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim pt As PivotTable
    Const strField1 As String = "xxxxxxxx"

    On Error Resume Next
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    If Target.Address = "$B$2" Then
        For Each ws In ThisWorkbook.Worksheets
            For Each pt In ws.PivotTables
                With pt.PageFields(strField1)
                .ClearAllFilters
                .CurrentPage = Target.Value
                 End With
            Next pt
        Next ws
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Be aware:
1. the code must be placed on the codesheet of the worksheet were you placed the target cell
2. change the XXXXX part in the code to the name of the filtered cell.

 

[Excel reporter1979]

I have found underneath basic VBA code to change one filter in one PT based on one cell value in another sheet. This code works correct. But What I would like to achieve is to change multiple PT's with two different cell values (year) + (month) and change multiple PT's with multiple filters in one PT.

Situation:
Cell value E37 (sheet1), changes PT4 (sheet2) the "Case closed (year)" Filter.
Cell value E38 (sheet1), changes PT4 (sheet2) the "Case closed (month)" Filter.
Cell value E37 (sheet1), changes PT5 (sheet2) the "Case created (year)" Filter.
Cell value E38 (sheet1), changes PT5 (sheet2) the "Case created (month)" Filter.
etc.

How do you apply this on underneath VBA code?


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


'This line stops the worksheet updating on every change, it only updates when cell
'E37 or E38 is touched
If Intersect(Target, Range("E37:E38")) Is Nothing Then Exit Sub

'Set the Variables to be used
Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String

'Here you amend to suit your data
Set pt = Worksheets("3. Pivot month report v2").PivotTables("PivotTable4")
Set Field = pt.PivotFields("Case closed (year)")
NewCat = Worksheets("Toelichting").Range("E37").Value

'This updates and refreshes the PIVOT table
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With


End Sub