Pivot Table
 

Hello,
I have a pivot table where I analyze supplier performance. I have the
supplier name in the Page field. How can I determine in VBA the list of
suppliers contained in the field? I want to be able to give a person a drop
down box with a list of suppliers where they can pick the supplier name. I
know they are listed in the page field but I am putting the pivot table on a
hidden, protected sheet so they can't change it.

Thanks,

Bill

Pivot Table
 

Bill

Here's a function that will return a collection of all items in the
pagefield. It finds which column the pagefield uses and loops through that
column adding unique entries to the collection. There's not a lot in the
way of error checking in it, but it should give you a start.

Function UniquePageFields(pt As PivotTable) As Collection

Dim colUniques As Collection
Dim pf As PivotField
Dim lCol As Long
Dim rCell As Range
Dim rngSource As Range

Set pf = pt.PageFields(1)
Set rngSource = Range(Application.ConvertFormula(pt.SourceData, xlR1C1,
xlA1))

lCol = Application.WorksheetFunction.Match(pf.SourceName,
rngSource.Rows(1), False)

Set colUniques = New Collection

For Each rCell In rngSource.Columns(lCol).Cells
On Error Resume Next
colUniques.Add rCell.Text, rCell.Text
On Error GoTo 0
Next rCell

Set UniquePageFields = colUniques

End Function


Sub test()

Dim vItm As Variant
Dim colPages As Collection

Set colPages = UniquePageFields(Sheet1.PivotTables(1))

For Each vItm In colPages
Debug.Print colPages.Item(vItm)
Next vItm

End Sub


--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Bill wrote:
Hello,
I have a pivot table where I analyze supplier performance. I have the
supplier name in the Page field. How can I determine in VBA the list
of suppliers contained in the field? I want to be able to give a
person a drop down box with a list of suppliers where they can pick
the supplier name. I know they are listed in the page field but I am
putting the pivot table on a hidden, protected sheet so they can't
change it.
Thanks,

Bill

Pivot Table
 

Or, if the source data is an Excel worksheet, you could use an Advanced
Filter to extract the unique items from the page field's column, and
sort the results.

Dick Kusleika wrote:
Bill

Here's a function that will return a collection of all items in the
pagefield. It finds which column the pagefield uses and loops through that
column adding unique entries to the collection. There's not a lot in the
way of error checking in it, but it should give you a start.

Function UniquePageFields(pt As PivotTable) As Collection

Dim colUniques As Collection
Dim pf As PivotField
Dim lCol As Long
Dim rCell As Range
Dim rngSource As Range

Set pf = pt.PageFields(1)
Set rngSource = Range(Application.ConvertFormula(pt.SourceData, xlR1C1,
xlA1))

lCol = Application.WorksheetFunction.Match(pf.SourceName,
rngSource.Rows(1), False)

Set colUniques = New Collection

For Each rCell In rngSource.Columns(lCol).Cells
On Error Resume Next
colUniques.Add rCell.Text, rCell.Text
On Error GoTo 0
Next rCell

Set UniquePageFields = colUniques

End Function


Sub test()

Dim vItm As Variant
Dim colPages As Collection

Set colPages = UniquePageFields(Sheet1.PivotTables(1))

For Each vItm In colPages
Debug.Print colPages.Item(vItm)
Next vItm

End Sub




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html