Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

extract items from slicer filtered by another slicer

0

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
Answer
Discuss

Discussion

Niro, thanks for updating your question with the answer. Next time, please put the answer in its own "Answer" to your question, at the bottom of the page, so people who visit this page in the future can more easily see that it is the answer.
don (rep: 1989) Jul 23, '17 at 10:58 am
Add to Discussion

Answers

0
Selected Answer

[Copied from Niro's Question as he Answered the issue himself and forgot to put it in an Answer]

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
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login