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