Link pivot table report filter to another cell

[richiet]

Hello

Is there a way to link the report filter value in a pivot table to another cell?

I have several pivot tables and rather than having to change the supplier code in the report filter on each one I would like to be able to enter a supplier code in a separate cell and use this to change the filters on all pivot table at once.

thanks for any help

Richie

 

[Siggi]

Hello Richie,

You could try this Worksheet_Change code.
Copy it into the sheet code of the worksheet that has the Cell you will use for the filter.
The example below assumes that is B2, and you can edit this to suit your needs.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Target.Address = Range("B2").Address Then Exit Sub
    Dim PT As PivotTable
    Dim ptItem As PivotItem
    On Error Resume Next
    For Each PT In Worksheets("Sheet1").PivotTables
        With PT.PivotFields("MyReportField")
            If .EnableMultiplePageItems = True Then
                .ClearAllFilters
            End If
            Set ptItem = .PivotItems(Target.Value)
            If Not ptItem Is Nothing Then
                .CurrentPage = Target.Value
            End If
        End With
    Next
End Sub

ear Jerry,

thank you very much for the help. That works very well. At first I had problems as the reference cell was not recognized (year in my case). I had to change the cell format to text and it worked.
I have another question regarding the work around. I try to include two filter from two reference cells. I tried to adress the two PivotFields with an &, but then I get stuck in defining the target value as there are 2. Is there a possible workaround?

Thank you very much,
D

 

[MarkR3003]

Hi Jerry

The code works well but how would I amend the code to get the pivot table to auto-refresh if the value in B2 changes, as B2 is linked to another cell in another worksheet. Currently, I have to press 'Return' in B2 to get the pivot table to refresh

Should I be using PT.RefreshTable somewhere in the code?

Many thanks

Mark

 

[MarkR3003]

Hi Jerry

The code works well but how would I amend the code to get the pivot table to auto-refresh if the value in B2 changes, as B2 is linked to another cell in another worksheet. Currently, I have to press 'Return' in B2 to get the pivot table to refresh

Should I be using PT.RefreshTable somewhere in the code?

Many thanks

Mark​

 

[sajarac]

Hi there, sorry to bother you, if you don't mine would you like to point me in the right direction?

I guess this topic is what I am looking for:

I have a legacy control, a dropdown that takes the value from a column in another list.

Once I select the value it is link to a cell in my "Sheet1", after that I have several lookup fields that check that cell value and return the values required. But also I have some Pivot tables that I have to manually select the same "value" fromm my dropdown".

Correct me if I am wrong? is possible to have the filter field getting the value from my linked cell ? I just want to select the value once.

Thanks in advance for any advice,


Regards