In Excel 2007, Microsoft added structured tables. Although far from being my favourite Excel feature, they are certainly a step in the right direction, and Microsoft are using them more and more in other functions.
Excel 2010 saw the addition of slicers with pivot tables, a feature that provides a much more intuitive and visual filtering of pivot tables.
Excel 2013 gave us the ability to add a slicer to a structured table (see the step-wise progress here :D?), definitely a welcome improvement. One of the great features of slicers with pivot tables is that you can link the slicer to multiple pivots, synchronised filtering. Unfortunately, in their wisdom Microsoft did not see fit to provide similar functionality to slicers attached to structured tables.
The question as to whether you can control multiple tables from one slicer, and the answer is invariably no. Recently this question was asked on LinkedIn, so I built such a facility, One Slicer To Control Them All.
Slicers cannot control multiple tables, so my approach is to create a pivot table from one of the tables, designated as the master table in this context, and add slicer(s) for this pivot. Selecting from the slicer will ripple down to the pivot table, and changes to this table can be trapped using the PivotTableUpdate event. By trapping the changes in this event, we can then filter all tables for this criteria.
That is basically all that is required, but to cater for any changes in the master table, changes that wouldn’t automatically reflect any slicer selections, I trap those changes using the SelectionChange event and refresh the pivot (which will then trigger the PivotTableUpdate event, thereby filtering the new data accordingly). I am only trapping the master table, it would not be difficult to trap a change to any of the tables.
One other thing it caters for is the situation where one or more of the tables has headers suppressed (why you would do this is beyond me, but there is the option in the Table Style Options group of the Table context ribbon), in which case when testing the HeaderRowRange, which is done to find which column to filter, nothing is returned, so nothing gets filtered. This was pointed out to me by Zack Barresse, an Excel MVP who wrote the book on structured tables, and he also provided me with a function to resolve this. This function, HeaderRowlabels in the code below, returns an array of column headings regardless of this header setting. By checking the slicer caption against this array, I can get the column regardless. I also added a check in my code to test whether headers are shown, as the filter restores the headings when suppressed, so I need to reset to the user’s choice.
Outlining the steps in a little more detail:
First, I create a pivot table from the table that is designated as the master table, and I display all of the items to that pivot (you can hide it far off to the right, or on a hidden sheet, if you don’t want it to be shown).
I then add slicer(s) to the pivot table for my filter fields. The VBA traps the Worksheet_PivotTableUpdate event, and iterates all tables to filter by the slicer criteria.
The names of the master table and the controlling pivot table are setup as module constants to facilitate maintenance.
It is possible that the user has table headers turned off, in which case .HeaderRowRange returns nothing, the function HeaderRowLabels gets an array of header labels regardless of this setting.
This code assumes that all of the tables are on the same sheet, if they are spread across multiple sheets the code will need to be amended to cater for this.
Managing Table Additions/Changes
As users will be adding to the tables, and as the pivot does not automatically update, it needs to be refreshed which is managed from code.
The Worksheet_SelectionChange code checks if any cell that is changed is within the master table, the table that the pivot is generated from, it refreshes the pivot table, a refresh that does ripple through to the slicers.
This could easily be changed to check all tables.
This technique has clear limitations, it is almost entirely predicated upon the assumption that the tables are of the same structure. The slicers do not get updated if any of the tables other than the master table is updated.
Within those constraints it seems to work well, and some of the constraints can be mitigated with code changes.
Option Explicit Const TABLE_MASTER As String = "tblMaster" Const PIVOT_NAME As String = "pvtControl" Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) Dim Slicer As SlicerItem Dim Table As ListObject Dim Headers As Boolean Dim values As Variant Dim nextrow As Long Dim idx As Long Dim i As Long For i = 1 To Target.Slicers.Count nextrow = 1 ReDim values(1 To nextrow) For Each Slicer In Target.Slicers(i).SlicerCache.VisibleSlicerItems ReDim Preserve values(1 To nextrow) values(nextrow) = Slicer.Name nextrow = nextrow + 1 Next Slicer For Each Table In Me.ListObjects Headers = Table.ShowHeaders idx = Application.Match(Target.Slicers(i).Caption, HeaderRowLabels(Table), 0) If nextrow > Target.Slicers(i).SlicerCache.SlicerItems.Count Then Table.Range.AutoFilter Field:=idx Else Table.Range.AutoFilter Field:=idx, Criteria1:=values, Operator:=xlFilterValues End If Table.ShowHeaders = Headers Next Table Next i End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim tableCell As Boolean On Error Resume Next tableCell = Target.ListObject.Name = TABLE_MASTER On Error GoTo 0 If tableCell Then Me.PivotTables(PIVOT_NAME).PivotCache.Refresh End Sub Function HeaderRowLabels(ByVal Table As ListObject) As Variant Dim TableColumn As ListColumn Dim TableHeaders() As Variant If Table.ShowHeaders Then ReDim TableHeaders(1 To Table.ListColumns.Count) If UBound(TableHeaders) = 1 Then TableHeaders(1) = Table.HeaderRowRange.Value Else TableHeaders = Table.HeaderRowRange.Value End If HeaderRowLabels = TableHeaders Else ReDim TableHeaders(1 To Table.ListColumns.Count) For Each TableColumn In Table.ListColumns TableHeaders(TableColumn.Index) = TableColumn.Name Next TableColumn HeaderRowLabels = TableHeaders() End If End Function