Filter Pivot Table based on Cell Value

[FlashNZ]

Hi, I have a Macro that changes the filter on a Pivot Table based on a cell value. It works fine if I click in the cell and hit Enter, then the PT updates.

What I want is for it to work when the reference cell changes based on the selection of a separate Slicer (which is connected to a different PT), Here is my code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
'This line stops the worksheet updating on every change, it only updates when cell
'H6 or H7 is touched
If Intersect(Target, Range("AL1:AL2")) 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("PT's").PivotTables("PivotTable3")
Set Field = pt.PivotFields("FinancialYear")
NewCat = Worksheets("PT's").Range("AL1").Value
 
'This updates and refreshes the PIVOT table
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With
 
End Sub

 

[RoryA]

You could use the Worksheet_Pivottableupdate event for the other pivot table to trigger it whenever that pivot is changed?

 

[FlashNZ]

HI Roy, thanks for the suggestion but not sure how to implement that?

 

[RoryA]

Is the pivot table connected to the slicer on a different worksheet, and is it the only pivot on that sheet? If not, what is the pivot table's name?

 

[FlashNZ]

The Slicer is on another sheet and that slicer is connected to three pivot tables: PivotTable1, PivotTable2 & PivotTable4. The Pivot table I want to filter is PivotTable3 as this is based on a different data set. Hope this explains it ok?

 

[RoryA]

You could use something like this in a/the sheet where the pivot tables connected to the slicer are:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
 
'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("PT's").PivotTables("PivotTable3")
Set Field = pt.PivotFields("FinancialYear")
NewCat = Worksheets("PT's").Range("AL1").Value
 
application.enableevents = false
'This updates and refreshes the PIVOT table
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable

application.enableevents = true
End Sub

Note: I removed your With block as it wasn't actually doing anything.

 

[FlashNZ]

Hi Rory,

Worked perfectly, thanks.

Gordon

 

[RoryA]

Glad we could help.

 

[Shooter_99999]

Hi

Looking for some assistance, based on a variation on this.

The entries in D1, D2, D3 and D5 will vary based on user action.

How can I adjust the above VBA so that the pivot table is filtered on the entries in D1, D2, D3 and D5.

All assistance grealy appreciated/