The code assumes that your pivot table on a worksheet named Pivot and
the pivot chart is on a chart sheet named Chart1.

marina madeleine wrote:
Debra,

A while back I had inquired about how to do a "show pages" for the excel
pivot charting function, and you had suggested the following code to do
this (attached below). I tried out the code, but somehow keep coming up
with the following error:

run-time error 9
subscript out of range

It seems to crop up at this line:
Set pt = Worksheets("Pivot").PivotTables(1)

Do you know why this is coming up and how to fix it? Does it matter
where in the worksheet the macro is run from?

Thanks.

Marina

Sub PrintPivotChart()
'prints a chart for each item in the page field
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

Set pt = Worksheets("Pivot").PivotTables(1)
For Each pf In pt.PageFields
For Each pi In pf.PivotItems
pt.PivotFields(pf.Name).CurrentPage = pi.Name
' ActiveWorkbook.Charts("Chart1").PrintOut
ActiveWorkbook.Charts("Chart1").PrintPreview
Next
Next pf
End Sub



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


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