Note the “Readout” that Displays the User’s Slicer Selections:
Now Do This for Every Pivot in Your Workbook With One Click!
A Common Trick, Now Automated
This is something we do all the time at Pivotstream – we write formulas that capture user slicer selections and then display those selections back to them.
We do this via hidden report filters:
(Yes, we could also do this with cube formulas, but we started out (literally years ago now) using this approach and we’ve just kinda stayed with it. I’m not sure cube formulas would be better, but they might be.)
Why is the Readout Useful?
Why do we do this? Well, for one thing, the Download Snapshot feature on the server does NOT download the slicers – you get a big blank white space where the slicers were, which isn’t terribly helpful. It leaves you wondering what you had selected.
It’s also useful when there are slicers on other sheets impacting your current sheet. And even on a single sheet, it’s often nice to have a compact readout of your selections without having to scroll (or even scan with your eyes) to see what selections you have made.
Isn’t This a Major Hassle, Adding All the Report Filters, etc?
You bet it is. It’s a major headache. But yesterday I wrote some macros that do it all for us 
The macros don’t run at all while the user is running the report. In fact they don’t even have to be saved in the workbook (the workbook can remain an XLSX, no need to save it as XSLM or XLSB).
Summary of What the Macros Do
- Look at every sheet in the workbook
- If the sheet is visible, AND it contains a pivot, create a new sheet (a “clone” sheet), named similar to the original but with the suffix “Ctxt” – this is short for “Context,” which is what we call the readout
- Create a pivot on the new sheet
- For every slicer on the original pivot, create a report filter on the new pivot
- Connects all of the slicers on the original pivot to the new pivot
- Creates a bunch of formulas on the new sheet that ultimately converge in cell G1
- Hides the new sheet
- Moves on to the next sheet/pivot
Instructions
If you are unfamiliar with macros, go watch the first few minutes of this video – that will make these steps much easier to understand.
- Copy the macros below
- Using the Developer tab on the Excel Ribbon (you may have to unhide this tab – it is hidden by default), launch the Visual Basic window
- Paste the macros into a Module – either in the current workbook or in your personal.xlsb (which you may have to create
- Run the CloneAllVisiblePivots macro
- Wait for it to complete
- On your report sheet (named ReportSheet for instance), create a formula that references G1 on the hidden version of that sheet (=ReportSheetCtxt!G1 for instance):
Last step: reference cell G1 on the Ctxt version of the report sheet
Limitations
As always, I write my macros like it’s a prison fight – down and dirty, whatever gets the job done
I know for certain that these macros fail if you have more than one pivot on a sheet, or if you have sheets with long names that differ only in the last few characters.
I’m sure they fail in other cases too, but those are the ones I am aware of.
Here They Are
Copy paste these into your Personal.XLSB or wherever you want to put them:
Sub CloneAllVisiblePivots()
Dim oSheet As Worksheet
Dim oPivot As PivotTable
For Each oSheet In ActiveWorkbook.Sheets
If oSheet.Visible = xlSheetVisible Then
If oSheet.PivotTables().Count > 0 Then
CloneCurrentPivotForContextFormulas oSheet.Name, oSheet.PivotTables(1).Name
End If
End If
Next
End Sub
Sub CloneCurrentPivotForContextFormulas(sSheet As String, sPivot As String)
Dim oSourceSheet As Worksheet
Dim oDestSheet As Worksheet
Dim oSourcePivot As PivotTable
Dim oDestPivot As PivotTable
Set oSourceSheet = ActiveWorkbook.Sheets(sSheet)
Set oSourcePivot = oSourceSheet.PivotTables(sPivot)
‘Create and rename new sheet
Set oDestSheet = ActiveWorkbook.Sheets.Add
If Len(oSourceSheet.Name) <= 27 Then
oDestSheet.Name = oSourceSheet.Name & “Ctxt”
Else
oDestSheet.Name = Left(oSourceSheet.Name, 27) & “Ctxt”
End If
‘Create pivot on new sheet
ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
ActiveWorkbook.Connections(“PowerPivot Data”), Version:=xlPivotTableVersion14 _
).CreatePivotTable TableDestination:=oDestSheet.Cells(1, 1), _
TableName:=”PivotTable1″, DefaultVersion:=xlPivotTableVersion14
Set oDestPivot = oDestSheet.PivotTables(1)
‘Create page filters on second pivot, connect slicers, add formulas
If oSourcePivot.Slicers().Count > 0 Then
CreatePageFilterForEachSlicerOnClonedPivot oSourceSheet.Name, oSourcePivot.Name, oDestSheet.Name, oDestPivot.Name
ConnectAllSlicersOnPivot1ToPivot2 oSourceSheet.Name, oSourcePivot.Name, oDestSheet.Name, oDestPivot.Name
CreateContextFormulas (oDestSheet.Name)
End If
‘Hide cloned sheet
oDestSheet.Visible = xlSheetHidden
End Sub
Sub CreateContextFormulas(sSheet As String)
ActiveWorkbook.Worksheets(sSheet).Activate
‘Add first row of formulas
Cells(1, 3).FormulaR1C1 = “=IF(RC[-1]<>””(All)””,1,0)”
Cells(1, 4).FormulaR1C1 = “=IF(SUM(C[-1])>1,””,””, “”””)”
Cells(1, 5).FormulaR1C1 = “=IF(RC[-4]=””””,””””,(IF(RC[-3]=””(All)””,””””,RC[-4]&”” = “”&RC[-3]&RC[-1])))”
‘Now fill those three formulas down
Range(“C1:E1”).Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range(“C1:E15”), Type:=xlFillDefault
‘Now add the formula that concats them all together
Cells(1, 7).FormulaR1C1 = “=””Selection: “” &RC[-2]&R[1]C[-2]&R[2]C[-2]&R[3]C[-2]&R[4]C[-2]&R[5]C[-2]&R[6]C[-2]&R[7]C[-2]&R[8]C[-2]&R[9]C[-2]&R[10]C[-2]&R[11]C[-2]&R[12]C[-2]&R[13]C[-2]&R[14]C[-2]”
End Sub
Sub CreatePageFilterForEachSlicerOnClonedPivot(sSourceSheet As String, sSourcePivot As String, sDestSheet As String, sDestPivot As String)
Dim oSlicer As Slicer
Dim oSlicerCache As SlicerCache
Dim sField As String
Dim oPivot1 As PivotTable
Dim oSheet1 As Worksheet
Dim oPivot2 As PivotTable
Dim oSheet2 As Worksheet
Set oSheet1 = ActiveWorkbook.Sheets(sSourceSheet)
Set oPivot1 = oSheet1.PivotTables(sSourcePivot)
Set oSheet2 = ActiveWorkbook.Sheets(sDestSheet)
Set oPivot2 = oSheet2.PivotTables(sDestPivot)
For Each oSlicer In oPivot1.Slicers
sField = oSlicer.SlicerCache.SourceName
oPivot2.CubeFields(sField).Orientation = xlPageField
Next
End Sub
Sub ConnectAllSlicersOnPivot1ToPivot2(sSheet1 As String, sPivot1 As String, sSheet2 As String, sPivot2 As String)
Dim oSheet1 As Worksheet
Dim oPivot1 As PivotTable
Dim oSheet2 As Worksheet
Dim oPivot2 As PivotTable
Dim oSlicer As Slicer
Set oSheet1 = ActiveWorkbook.Sheets(sSheet1)
Set oPivot1 = oSheet1.PivotTables(sPivot1)
Set oSheet2 = ActiveWorkbook.Sheets(sSheet2)
Set oPivot2 = oSheet2.PivotTables(sPivot2)
For Each oSlicer In oPivot1.Slicers
oSlicer.SlicerCache.PivotTables.AddPivotTable oPivot2
Next
End Sub
