Collect Pivot Table's Selected Labels

J

jerem

New member
Joined
Nov 3, 2011
Messages
3
Reaction score
0
Points
0
Location
Hong Kong
Website
www.iexcelconsulting.com
Hi,
I have a pivot table with slicers. I would like to collect into a cell all the item names that have been selected (filtered). (through VBA or Formula)
For example if I select A, D, and E into the (A, B, C, D, E, F) list. I want the labels A, D, E to appear in a cell.
Is there a way?
Thanks!

This has been cross posted but the Forum does not allow me to paste the links.
 
I came up with this and it did the trick

Code: 
Sub CollectCaptions()
Dim r As String
Dim i As Integer, p As Integer
    With ActiveWorkbook.SlicerCaches("Slicer_Type")
        i = .SlicerItems.Count
        For p = 1 To i
            If .SlicerItems(p).Selected = True Then
                r = r + .SlicerItems(p).Caption & ", "
            End If
        Next p
    End With
r = Left(r, Len(r) - 2)
MsgBox r
End Sub
 
Corrected...

Code: 
Sub CollectCaptions()
Dim r As String
Dim i As Integer, p As Integer
    With ActiveWorkbook.SlicerCaches("Slicer_Type")
        i = .SlicerItems.Count
        For p = 1 To i
            If .SlicerItems(p).HasData = True Then
                r = r + .SlicerItems(p).Caption & ", "
            End If
        Next p
    End With
r = Left(r, Len(r) - 2)
MsgBox r
End Sub
 
You must log in or register to reply here.
Back
Top