Results 1 to 4 of 4

Thread: Collect Pivot Table's Selected Labels

  1. #1
    Neophyte jerem's Avatar
    Join Date
    Nov 2011
    Location
    Hong Kong
    Posts
    3
    Articles
    0

    Collect Pivot Table's Selected Labels



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,356
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by jerem View Post
    This has been cross posted but the Forum does not allow me to paste the links.
    Here they are:
    http://www.ozgrid.com/forum/showthread.php?t=186217
    http://www.mrexcel.com/forum/excel-q...ed-labels.html
    http://www.excelforum.com/excel-gene...ed-labels.html

  3. #3
    Neophyte jerem's Avatar
    Join Date
    Nov 2011
    Location
    Hong Kong
    Posts
    3
    Articles
    0
    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

  4. #4
    Neophyte jerem's Avatar
    Join Date
    Nov 2011
    Location
    Hong Kong
    Posts
    3
    Articles
    0
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •