Macro to read data from Pivot Table into UserForm Listbox.

[ZenZilla]

Hello,

I am creating a userform, and I want one of the fields on the form to be populated from a pivot table that can be filtered using (currently, this may change) 5 slicers. So far, the macro I have successfully populates the listbox with the data, but it includes Pivot Items that have been removed by the slicers. I need the populated data to be dynamic based on these slicers. My code so far below:

Edit: and yes I have already set "number of items to retain per field" to "none". Still doesn't work.

Private Sub UserForm_Initialize()
    Dim strTimeArray() As String
    Dim intTimeCounter As Integer
    Dim strDayExArray() As String
    Dim intDayExCounter As Integer
    Dim PT As PivotTable
    Dim PF As PivotField
    Dim intEmailCount As Integer
    'Dim lngEmailList As Long

Set PF = Worksheets("Distribution List").PivotTables("Distribution List").PivotFields("Email")
        With RefineAttendeesIncludeList
            For intEmailCount = 1 To PF.PivotItems.Count
                .AddItem PF.PivotItems(intEmailCount)
            Next
        End With

Any help would be much appreciated!

 

[ZenZilla]

Bump

 

[ZenZilla]

Bump. I have attempted to use .currentPage but I am not sure that I am using it correctly. I cant seem to find anything on google that fixes this issue.

 

[ZenZilla]

I have found a solution that worked for my case, however it is not actually reading directly from the pivot table, it is just reading from a range. So, for future reference if anyone googles this:

Dim intEmailCount As Integer
    Dim strEmailList() As String

ReDim strEmailList(ActiveSheet.UsedRange.Rows.Count - 1)
    intEmailCount = 1
    Do Until ActiveCell = ""
        RefineAttendeesIncludeList.AddItem (ActiveCell)
        strEmailList(intEmailCount) = ActiveCell
        intEmailCount = intEmailCount + 1
        ActiveCell.Offset(1, 0).Select
    Loop

I believe it may be possible to do what I originally wanted using something with ActiveCell.PivotCell.PivotItem but I couldn't figure it out.