Hi,
i need to extract selected slicer items from a dashboard, but when i run the code i get items that are selected but filtered by another slicer and therefore not visible on this slicer, i need to get only the visible (not filtered) items that are selected.
here is my code so far:
Edit: found the answer, adding osi.hasdata to the loop, it still goes over all the items in the slicer but it at least doesnt refer to the items not visible.
Public Function GetSelectedSlicerItems(SlicerName As String) As String
Dim oSc As SlicerCache
Dim oSi As SlicerItem
Dim Sh As Worksheet
Dim Loc As Range
On Error Resume Next
Dim DateFind(6) As Date
Application.Volatile
i = 0
Set oSc = ActiveWorkbook.SlicerCaches(SlicerName)
'get slicer items
If Not oSc Is Nothing Then
For Each oSi In oSc.SlicerItems
If oSi.HasData Then
If oSi.Selected Then
GetSelectedSlicerItems = 1
DateFind(i) = oSi.Name
i = i + 1
End If
End If
Next
End If
'go to each selected items location
For i = 0 To 5
Set Loc = Cells.Find(What:=DateFind(i))
If Not Loc Is Nothing Then
Loc.Select
Set Loc = Nothing
End If
Next i
End Function