Filter Pivot Table based on Cell Value

FlashNZ

New Member
Joined
Mar 3, 2021
Messages
29
Office Version
  1. 365
Platform
  1. Windows
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:

VBA 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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
You could use the Worksheet_Pivottableupdate event for the other pivot table to trigger it whenever that pivot is changed?
 
Upvote 0
HI Roy, thanks for the suggestion but not sure how to implement that?
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
You could use something like this in a/the sheet where the pivot tables connected to the slicer are:

VBA Code:
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.
 
Upvote 0
Solution
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.


1664458270956.png




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/
 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,206
Members
453,022
Latest member
RobertV1609

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top