populate userform listbox with pivotRowField items

[ejvenema]

Hi there,

I'm an average vba programmer (actualy a mathteacher and an exam manager at a k12 (kind off) school in the Netherlands).

And I'm realy stuck within vba with the following: I try to populate an userform listbox with pivotrow.pivotitems of a filtered pivottable. And I'm searching the web for three days now and can't find a solutiond.

Situation: I have an Excel workbook with several worksheets. A main sheet (kind of a daschboard where some selections can be made), a hidden lookup sheet and a student sheet (an excel table with student info). On the student sheet, students are assigned to courses and within are assigned to groups (because exam groups cant's be to big). On the hidden lookup sheet I defined a pivot table that shows the groupnames and the assinged studentnames depending on the selected course (from the main sheet). So far so good. Populating a listbox isn't a problem, but to retreve an array with groupnames from the pivotrows and an array with assigned studentnames from the pivottable is a problem...

Could anyone help me out with this?

Kind regards,
Eric-Jan Venema

 

[Worf]

Welcome to the Board

One way to do it is to copy the filtered pivot to another location, converting it to a table or ordinary range.
The necessary data can be retrieved from this range.
A pivot table often has blank cells in its body; tell me if you need help with that.
Naturally, the code below is just an example and will require some tweaking.

Sub Neder()
Dim pt As PivotTable, r As Range, myr As Range, groupname, students
Set pt = ActiveSheet.PivotTables(1)
Range(pt.TableRange1.Address).Copy [ba1]                            ' convert into range
Set r = [ba1].CurrentRegion
Set myr = r.Find("Actual", , xlValues).Resize(, r.Columns.Count)    ' searches for row label "Actual"
Set myr = Range(myr.Cells(1, 2), myr.Cells(1, myr.Columns.Count))   ' exclude first column
groupname = WorksheetFunction.Transpose(myr.Value)
MsgBox UBound(groupname) & " group names"


Set myr = r.Find("Jan", , xlValues).Resize(r.Rows.Count)            ' searches for column label "Jan"
Set myr = Range(myr.Cells(3, 1), myr.Cells(myr.Rows.Count, 1))      ' exclude first two rows
students = myr
MsgBox UBound(students) & " students"
End Sub

 

[ejvenema]

Thanks Worf! This helped a lot...

 

[Worf]

You are welcome.