Well. I have a table named "ALL_INFO" on a Sheet in Excel and I made a Pivot table in other sheet, its name is "PIVOT_INFO". And I would like to know the way to link a table, and a pivot table using Slicers to filter information and it be reflected in both tables.
Anybody knows how I can do it?
Thank you in advance.
Create a Slicer for the PivotTable, and one for the Table. Make sure the PT Slicer is visible, and the Table Slicer is hidden somewhere where users can't see it. Then put this code in the Sheet Module corresponding to the worksheet where your PT is:
Option Explicit
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim sLastUndoStackItem As String
Dim sc_Pivot As SlicerCache
Dim sc_Table As SlicerCache
Dim si_Pivot As SlicerItem
Dim si_Table As SlicerItem
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
If Target.Name = "PivotTable1" Then '<= Change name as appropriate
On Error Resume Next 'in case the undo stack has been wiped or doesn't exist
sLastUndoStackItem = Application.CommandBars(14).FindControl(ID:=128).List(1) 'Standard Commandbar, undo stack
'The above line doesn't seem to work in my version of O365 so we'll use the English language backup
If sLastUndoStackItem = "" Then sLastUndoStackItem = Application.CommandBars("Standard").Controls("&Undo").List(1)
On Error GoTo 0
If sLastUndoStackItem = "Filter" Or sLastUndoStackItem = "Slicer Operation" Then
Set sc_Pivot = ActiveWorkbook.SlicerCaches("Slicer_Data") '<= Change name as appropriate
Set sc_Table = ActiveWorkbook.SlicerCaches("Slicer_Data1") '<= Change name as appropriate
sc_Table.ClearAllFilters
On Error Resume Next 'In case items differ between Table and PT
For Each si_Pivot In sc_Pivot.SlicerItems
With si_Pivot
sc_Table.SlicerItems(.Name).Selected = .Selected
End With
Next si_Pivot
End If
End If
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub
Here's how things look before I use the "Master" slicer:
...and here's how things look after I use the "Master" slicer:
Note that filtering a Table hides rows in the entire worksheet. So you don't want to put anything that you want to remain visible at all times alongside the Table.
