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