Pivot/Table filter activated through link from other table's cell value

Question

I have a list of items to work through and some items require deeper analysis. Essentially I have two tables. The first shows production results and through filtering column U, I get a list of outliers as in the following image.

Table of outliers

The values displayed in column S then require further analysis for which a pivot chart has been set up. In this chart I filter the particular VRTY number to take a closer look.

Pivot Chart for analysis

Both sheets are contained in the same workbook and I essentially work with two windows open, but to go through the list I have to manually enter every single VRTY value in the pivot filter.

The table of the Pivot chart and the outlier table are not related and data sources are different.

In column S of the outlier table (VRTY) I would ideally turn the values into links that automatically set the pivot filter to this value when clicked.

I am a novice at VBA but from the research I've done this will be the only option - I just haven't come by an instructions how to achieve this particular function.

Instructions/ advice would be highly appreciated.

Answer 1

Sambo: Make sure the orientation of the 'Number' field in the PivotChart's underlying PivotTable is a PageField, and then put the below code in the Sheet code module that corresponds to the sheet where the Outliers PivotTable is:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim pf          As PivotField
    Dim pf_Slave    As PivotField

    Set pf = ActiveSheet.PivotTables("PivotTable1").PivotFields("VRTY")
    Set pf_Slave = ActiveSheet.PivotTables("PivotTable2").PivotFields("number")


    If Not Intersect(Target, pf.DataRange) Is Nothing Then
        On Error Resume Next
        pf_Slave.CurrentPage = CStr(Target)
        On Error GoTo 0
    End If

End Sub

Change "PivotTable1" and "PivotTable2" as appropriate.

By "Sheet code module" I mean this type of thing: