Selecting a PivotItem in code
 

Im looking for a way to loop through the items in a PivotField object.
Each item (a page field) should be selected and the results of the
pivot table printed out. For example, if the page field in a pivot
table were named "Fruit" and had three pivot items "Apples",
"Oranges", "Pears"; the code would select "Apples" and print the
results of the pivot table, select "Oranges" and print the results of
the pivot table, etc...
There's one caveat to this tho. The list of pivot items is not the
same every time. Sometimes it may only contain "Apples", "Pears";
other times it may only contain "Oranges", "Pears".
Ideally I'd like the code to go something like this...

Dim intCount as integer
With PivotTables("Table1")
For intCount = 1 to .PivotFields("Fruit").PivotItems.Count
.PivotFields("Fruit").PivotItems(intCount).Select
'Print code goes here.
Next intCount
End With

Unfortunately, there is no Select method for the PivotItem class.
When i use the macro recorder, it uses the CurrentPage property of the
PivotFields class. The CurrentPage property causes problems because if
the item isnt in the list, it renames the current item to the name i
was trying to select (its a read/write property instead of a read only
property).
Any help is appreciated and other ideas are welcome. Thanks in
advance.

Selecting a PivotItem in code
 

The following code will print (or preview) a copy of the pivot table for
each item in the page field:

'=================================
Sub PrintPivotPages()
'prints a copy of pivot table for
'each item in page field
On Error Resume Next
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set pt = ActiveSheet.PivotTables.Item(1)
For Each pf In pt.PageFields
For Each pi In pf.PivotItems
pt.PivotFields(pf.Name).CurrentPage = pi.Name
' ActiveSheet.PrintOut 'use this for printing
ActiveSheet.PrintPreview 'use this for testing
Next
Next pf
End Sub
'=======================================

Robert E. Peterson wrote:
Im looking for a way to loop through the items in a PivotField object.
Each item (a page field) should be selected and the results of the
pivot table printed out. For example, if the page field in a pivot
table were named "Fruit" and had three pivot items "Apples",
"Oranges", "Pears"; the code would select "Apples" and print the
results of the pivot table, select "Oranges" and print the results of
the pivot table, etc...
There's one caveat to this tho. The list of pivot items is not the
same every time. Sometimes it may only contain "Apples", "Pears";
other times it may only contain "Oranges", "Pears".
Ideally I'd like the code to go something like this...

Dim intCount as integer
With PivotTables("Table1")
For intCount = 1 to .PivotFields("Fruit").PivotItems.Count
.PivotFields("Fruit").PivotItems(intCount).Select
'Print code goes here.
Next intCount
End With

Unfortunately, there is no Select method for the PivotItem class.
When i use the macro recorder, it uses the CurrentPage property of the
PivotFields class. The CurrentPage property causes problems because if
the item isnt in the list, it renames the current item to the name i
was trying to select (its a read/write property instead of a read only
property).
Any help is appreciated and other ideas are welcome. Thanks in
advance.


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