Changing Columns/Series in Pivot Tables when Filter is changed

[mdp22]

I am building a model where when regions will be selected via a filter in a slicer but need it to change which field is brought in depending on which region is selected. In other words, I want the slicer to stay so that you can easily select the region, but I need the column headers in the pivot to change when the region does. So when "Region B" is selected in the filter, "Region B Grouping" shows up in the columns instead of "Region A Grouping" since it would be a pain (and easy for other people looking at the model to make a mistake) to have to change what goes in the column each time. Is this possible?

 

[Worf]

Welcome to the Board

To do that we need to know which slicer item was selected. Instead of writing a loop, here is a non-VBA solution by
Jeffrey Weir:

• Make a copy of the Pivot Table
• Remove all fields from the copy other than the one that the Slicer controls.
• Make that remaining field a Page Field, and then connect that new Pivot Table to the Slicer. This takes up just two cells.
• Any time anyone clicks the Slicer, that two-cell Pivot Table will be filtered on that selection automatically.
• On my example, the desired cell is AP86.

The code below will change one pivot table cell based on the slicer selection.

' sheet module
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
If Target.Name = "Pivot23" Then Target.TableRange1.Cells _
(1, Target.TableRange1.Columns.Count) = Me.[ap86] & " Grouping"
End Sub