I'm trying to write a macro so that when users of a workbook drill in on a pivot table, that drill in has the related slicer filters applied to the drill in results (why in god's name Microsoft doesn't already do this is beyond me).
The workbook in question has two pivot tables based on separate data and I've already worked out (most) of the logic for checking if any slicers are even applying a filter, and if so which value(s) are selected. I also have logic on the Before_DoubleClick & Worksheet_Activate actions that lets the macro know that a drill in is occurring (excluded as I've used the code before and know it works).
The last step is to find the column the slicer is based on and delete rows from the drill-in sheet where the column has a value that is NOT selected. I thought I'd be looking for SlicerCache.Levels.SourceFiled based on recording the creation of slicer, but that doesn't appear to be available when I tried to write the code (at least it didn't show in the AutoComplete options).
My current code to actually filter the drill in follows:
Sub Filter_Drill_Down(SourceSheet As Worksheet, Drill_Down_Sheet As Worksheet)
'
Dim Slicer_Column As String
Dim Selected_Values As String
Dim Selected_Cnt As Long
Dim Total_Values As Long
Dim All_Slicers As SlicerCaches
Dim SCache As SlicerCache
Dim Slicer_Obj As Slicer
Dim S_Item As SlicerItem
Dim DDS_Row As Long
Dim No_Match As Boolean
Application.ScreenUpdating = False
'Capture all the slicers in the workbook (since they're not natively sheet specific)
Set All_Slicers = ThisWorkbook.SlicerCaches
'Iterate through the slicer caches
For Each SCache In All_Slicers
'Check if the current cache has a Slicer Object on the source sheet
For Each Slicer_Obj In SCache.Slicers
If Slicer_Obj.Shape.Parent Is SourceSheet Then
'In order to prevent wasting time applying filters for slicers where no filtering occurs, we'll first check how many items are selected. Reset the counters to make
'sure we don't miscount, and clear Selected_Values so we don't include incorrect values
Selected_Cnt = 0
Total_Values = 0
Selected_Values = ""
For Each S_Item In SCache.SlicerItems
If S_Item.Selected Then
'Current item is selected, increment the selected count & add the value to the selected values string. That way if we do need to filter we can just check if
' a row's value for the relevant column is in the string & delete rows where that's not true
Selected_Cnt = Selected_Cnt + 1
Selected_Values = Selected_Values & "|" & S_Item.Name & "|"
End If 'else the item isn't selected, just need to increment the total count which happens anyway
Total_Values = Total_Values + 1
Next
'Check if the total = selected
If Total_Values > Selected_Cnt Then
'We actually need to filter. The first step in that is knowing which column to check
Slicer_Column = "A"
No_Match = True
'Find the matching column
Do While Drill_Down_Sheet.Range(Slicer_Column & 1).Value <> "" And No_Match
'Check if the header for the current column is the one we're looking for
If Drill_Down_Sheet.Range(Slicer_Column & 1).Value = <X> Then'HERE'S WHERE I NEED HELP
No_Match = False
Else
'Move to the next column
Slicer_Column = ColNumToStr(ColStrToNum(Slicer_Column) + 1) 'ColNumToStr converts a # to it's letter equivalent (i.e. 27 = AA) & ColStrToNum does the inverse
End If
Loop
'Iterate through the rows. Column A is ALWAYS filled, so use it to control the loop
DDS_Row = 2
Do While Drill_Down_Sheet.Range("A" & DDS_Row).Value <> ""
'Check the value in the target column against the selected items string
If InStr(1, Selected_Values, "|" & Drill_Down_Sheet.Range(Slicer_Column & DDS_Row).Value & "|") = 0 Then
'Value isn't selected, delete the row. Note: Since this will make what had been row DDS_Row + 1 into row DDS_Row, we do NOT increment DDS_Row
Drill_Down_Sheet.Range(DDS_Row & ":" & DDS_Row).Delete xlShiftUp
Else
'Not a filtered value, move to the next row
DDS_Row = DDS_Row + 1
End If
Loop
End If 'all the values are selected, which means none are filtered, so do nothing
End If 'else the slicer does not appear on the Source_Sheet, meaning it's not one that applies to the pivot table being drilled into. Just move to the next cache
Next
Next
Application.ScreenUpdating = True
End Sub
Some brute force trial & error got me the answer. You should use SlicerCache.SourceName to get the field name.