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