Slicers and values in a cell

[juca73]

Hi,

Not sure if this is possible but here goes

I have a pivot table ( with just the profit/loss returns ) with 4 connected slicers ( season, League, Home form Colour and Away form colour )

Is it possible to show what the slicer selections are in different cells so then i can set up a macro to then show all the rows in the main database that meet those selected slicer selections.

Thanks

 

[Worf]

Hi

Sub Juca()
Dim MyArr(), i%, s$, dest As Range
Set dest = [p200]                               ' starting cell
For i = 1 To ThisWorkbook.SlicerCaches.Count    ' all slicers
    s = ThisWorkbook.SlicerCaches(i).Name
    If s Like "*X*" Then                        ' desired slicers
        MyArr = IL(s)
        Set dest = dest.Resize(1, UBound(MyArr) + 1)
        dest.Value = MyArr
        Set dest = dest.Offset(1)
    End If
Next
End Sub

Public Function IL(sn$)
Dim ShortList(), i%, sC As SlicerCache, sI As SlicerItem
i = 0
Set sC = ThisWorkbook.SlicerCaches(sn)
For Each sI In sC.SlicerItems
    If sI.Selected = True Then               'And sI.HasData = True
        ReDim Preserve ShortList(i)
        ShortList(i) = sI.Value
        i = i + 1
    End If
Next
IL = ShortList
End Function