Excel VBA - multiple filters on pivot table: one linked to filter other pivot table; one based on the cell I just clicked on in a selected range

Question

I am trying to compose a report that makes a pivot table based on location and product. The location filter should be the same as the filter of another pivot table on that worksheet (however, I cannot use slicers as the dataset is not the same). The product filter should change depending on which cell I just clicked on in a certain range.

I have tried quite a lot of stuff found on different fora and have adjusted to my needs, but seems to me the problem is that I need a private sub SelectionChange in order to dynamically change the product. However I would need a PivotTableUpdate private sub for the location filter. Can't have both on one sheet right? So how to deal with these cases? The code I am using for the productfilter is: Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim ws As Worksheet
    Dim pt As PivotTable

If Target.Column = 13 Then
  Range("M2").Value = Target.Value

End If
End Sub

This is basically copying the value from the clicked cell in column 13 to cell M2, which is the cell for my pivottable filter. Additional issue is that if I click on a cell which does not provide a valid filter entry, I get an error. How to solve that?

Thanks for the help!

If both pivottables share the same pivotcache, simply create a slicer for each filterfield, which you tie to both pivottables. The slicers can be on any (hidden) sheet but will ensure the filters are in synch. — jkpieterse
that's the thing. The 2 pivot tables do not use the same data — Greg Wilson

jkpieterse jkpieterse · Accepted Answer · 2017-06-22T15:32:28

If both pivottables are on the same sheet and have one page filter, it can be as easy as adding this to the ThisWorkbook module:

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
    Application.EnableEvents = False
    If Target.PageRange.Column <> Sh.PivotTables(1).PageRange.Column Then
        Sh.PivotTables(1).PageRange.Cells(1, 2).Value = Target.PageRange.Cells(1, 2).Value
    Else
        Sh.PivotTables(2).PageRange.Cells(1, 2).Value = Target.PageRange.Cells(1, 2).Value
    End If
    Application.EnableEvents = True
End Sub

Excel VBA - multiple filters on pivot table: one linked to filter other pivot table; one based on the cell I just clicked on in a selected range

1 Answers