Pivot Table - Page List
 

I recorded a macro to find out the syntax for choosing
which page in a pivot table I wish to look at:

ActiveSheet.PivotTables("PivotTable6").PivotFields
("Name").CurrentPage = "MIKE"

Now I would like to obtain a listing of all the pages in
that pivot table. Does anyone know how to get that list
of values? I will be using that list to create a separate
dropdown control. Selecting values from the dropdown
control will feed a macro which will set the pivot table
to the desired page, etc., etc.

Pivot Table - Page List
 

Mike,

You can apply a filter to the source data to extract unique elements from
the field that you are using as your page. Here's an example for column H of
the activesheet:

Option Explicit
Option Base 0
Sub ExtractUniqueValues()
Dim myCell As Range
Dim myVals() As Variant
Dim UniqueCount As Integer
Dim i As Integer

UniqueCount = 0

With Range("H1:H" & Range("H65536").End(xlUp).Row)
.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
For Each myCell In Intersect(Range("2:65536"), _
.SpecialCells(xlCellTypeVisible))
ReDim Preserve myVals(UniqueCount)
myVals(UniqueCount) = myCell.Value
UniqueCount = UniqueCount + 1
Next myCell
ActiveSheet.ShowAllData
End With
For i = LBound(myVals) To UBound(myVals)
MsgBox myVals(i)
Next i
End Sub

HTH,
Bernie
MS Excel MVP

"Mike Swintosky" wrote in message
...
I recorded a macro to find out the syntax for choosing
which page in a pivot table I wish to look at:

ActiveSheet.PivotTables("PivotTable6").PivotFields
("Name").CurrentPage = "MIKE"

Now I would like to obtain a listing of all the pages in
that pivot table. Does anyone know how to get that list
of values? I will be using that list to create a separate
dropdown control. Selecting values from the dropdown
control will feed a macro which will set the pivot table
to the desired page, etc., etc.

Pivot Table - Page List
 

Thanks Bernie. I'll add that to my toolbox!

Mike
-----Original Message-----
Mike,

You can apply a filter to the source data to extract
unique elements from
the field that you are using as your page. Here's an
example for column H of
the activesheet:

Option Explicit
Option Base 0
Sub ExtractUniqueValues()
Dim myCell As Range
Dim myVals() As Variant
Dim UniqueCount As Integer
Dim i As Integer

UniqueCount = 0

With Range("H1:H" & Range("H65536").End(xlUp).Row)
.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
For Each myCell In Intersect(Range("2:65536"), _
.SpecialCells(xlCellTypeVisible))
ReDim Preserve myVals(UniqueCount)
myVals(UniqueCount) = myCell.Value
UniqueCount = UniqueCount + 1
Next myCell
ActiveSheet.ShowAllData
End With
For i = LBound(myVals) To UBound(myVals)
MsgBox myVals(i)
Next i
End Sub

HTH,
Bernie
MS Excel MVP

"Mike Swintosky"
wrote in message
...
I recorded a macro to find out the syntax for choosing
which page in a pivot table I wish to look at:

ActiveSheet.PivotTables("PivotTable6").PivotFields
("Name").CurrentPage = "MIKE"

Now I would like to obtain a listing of all the pages in
that pivot table. Does anyone know how to get that list
of values? I will be using that list to create a
separate
dropdown control. Selecting values from the dropdown
control will feed a macro which will set the pivot table
to the desired page, etc., etc.


.