CODEWITHSUNDEEP
pivot-tableslicers
Gustavo Saraiva
Gustavo Saraiva

Reputation: 13

How to link a Table and a Pivot Table using more than one slicer in Excel?

I had the same problem about using slicers to link table and a Pivot Table. The ansewr provided by [jeffreyweir] was perfect! But in my case, I will need the solution for using 3 or more different slicers.

The initial solution can be found by following the link: How to link a Table and a Pivot Table using Slicers in Excel?

Many tks in advance and I hope I had follow the rules of this fantastic site in the right way! ;-)

Upvotes: 1

Views: 2182

Answers (2)

Lukas
Lukas

Reputation: 3

For people who have data table on a separate sheet from their pivot table, simply change

Set lo = Range(sTable).ListObject

to

Set lo = Sheets("table_sheet_name").Range(sTable).ListObject

and store the code in the pivot table sheet module.

Upvotes: 0

jeffreyweir
jeffreyweir

Reputation: 4824

Okay, so I've amended the code so that it only needs PivotTable slicers, and it uses those slicer setting to directly filter the Table. Note that you need to change the following lines in the code to match the names of your Table and PivotTable:

Const sPivot As String = "PivotTable1" '<= Change name as appropriate

Const sTable As String = "Table1" '<= Change name as appropriate

This code has to be pasted into the sheet module belonging to the worksheet where the Tables/PivotTables concerned are:

enter image description here

Option Explicit

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim sLastUndoStackItem As String
Dim sc          As SlicerCache
Dim si          As SlicerItem
Dim vItems      As Variant
Dim i           As Long
Dim lo          As ListObject
Dim lc          As ListColumn
Dim sTest       As String

Const sPivot As String = "PivotTable1" '<= Change name as appropriate
Const sTable As String = "Table1" '<= Change name as appropriate

If Target.Name = sPivot Then
    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 lo = Range(sTable).ListObject

        For Each sc In ActiveWorkbook.SlicerCaches
            On Error Resume Next
            sTest = sc.PivotTables(1).Name
            On Error GoTo 0
            If sTest = sPivot Then
                Set lc = lo.ListColumns(sc.SourceName)
                If sc.FilterCleared Then
                    lo.Range.AutoFilter Field:=lc.Index
                Else
                    ReDim vItems(1 To 1)
                    For Each si In sc.SlicerItems
                        If si.Selected Then
                            i = i + 1
                            ReDim Preserve vItems(1 To i)
                            vItems(i) = si.Name
                        End If
                    Next si

                    lo.Range.AutoFilter Field:=lc.Index, Criteria1:=vItems, Operator:=xlFilterValues
                    ReDim vItems(1 To 1)
                End If
            End If
        Next sc
    End If
End If


End Sub

And here it is in action:

enter image description here

enter image description here

enter image description here

Upvotes: 1

Related Questions