Automate moving between pages on a pivot table

[Guest]

Hi -

I am trying to automate moving through the pages of a pivot table so that I
can print each individual page. I have the code to print each page and how
to specify a specific page to move through - I just need help figuring out
the full list of pages to loop through. Alternatively, is there a function
that allows you to print each page on a pivot table. Note, the pivot table I
am working with only has one field on the page.

Thanks,
meryl miller

 

[Guest]

Which version of Excel are you using. Pviot Tables change in Excel 2002. In
any case there is an option to Show pages. This will create a sperate sheet
for every item in the pivot table (Heading items that is). Here is some code
that works in xl2000. Change FieldName to the name of the field you wnat to
traverse...

Sub test()
Dim pvtItem As PivotItem
Dim wks As Worksheet

Set wks = ActiveSheet
For Each pvtItem In wks.PivotTables(1).PivotFields("FieldName").PivotItems
MsgBox pvtItem.Value
Next pvtItem
End Sub

 

[Tom Ogilvy]

Sub AABBCC()
Dim pf As PivotField
Dim pi As PivotItem
Set pf = ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Header2")
For Each pi In pf.PivotItems
pf.CurrentPage = pi.Value
ActiveSheet.PrintPreview
Next
pf.CurrentPage = "(All)"

End Sub


Worked for me.