pivot table chart/graphing

[marina madeleine]

I had posted this question before and am wondering if anyone knows how
to do a "show pages" for an excel pivot table chart. This is to
generate the chart/graphs for the very many countries who have data in
the pivot table.

I am able to do a "show pages" for the table form of a pivot table. But
for the chart/graph I don`t seem to be able to find a "show pages" on
the wizard menu.

Is there a way to do this or would it be necessary to do this by visual
basic?

Thanks.

Marina Madeleine




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

 

[Debra Dalgleish]

Show Pages isn't available for a Pivot Chart. You could use code similar
to the following to print or preview the chart for each item in the page
field.

'==========================
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
'=============================

 

[Tom Ogilvy]

for two pagefields, assume both are set to the first item

this would print the combinations of
each pi in PF1 with PF2 equal to pi1
for PF1 equal last pi only and each pi in PF2

I posted some code to do this on 30 August. It is a bit rough, but does
recursive calls

http://groups.google.com/[email protected]

For examination,
It can write the combinations to sheet3 rather than printing by setting the
printflag.

--
Regards,
Tom Ogilvy

 

[Debra Dalgleish]

Thanks for the link, Tom. I had missed your original posting of that code.

 

[marina madeleine]

Thank you, that's a big help. I will try out that programming.

Marina


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

 

[marina madeleine]

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!

 

[Tom Ogilvy]

That would indicate you don't have a worksheet with the tab name of Pivot.
Perhaps there is a space at the beginning or end of the name on the actuall
tab. Rename the sheet Pivot and it should work.


If you have more than one pagefield, that code won't print all combinations.

 

[Debra Dalgleish]

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]

Debra and Tom,

Thanks for the explanation on the error. I'll change the worksheetname.
There was mention that the code would work only if there was only one
page field. If I have three page fields, two of which are fixed while
the third one is the "show pages" one - is it possible that this code
would work ok for that? How would it be possible to do this?

Marina


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

 

[Tom Ogilvy]

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)
set pf = pt.PageFields("ThisOne")
For Each pi In pf.PivotItems
pt.PivotFields(pf.Name).CurrentPage = pi.Name
' ActiveWorkbook.Charts("Chart1").PrintOut
ActiveWorkbook.Charts("Chart1").PrintPreview
Next

End Sub


Change "ThisOne" to the name of the pagefield you want to loop through.

 

[marina madeleine]

Tom,

Thanks for the code for "prints a chart for each item in the page
field". Just to clarify, for example if my page fields were:

country
indicator
measurement

where country is more than 50 countries
indicator can be "population 50+" or "total population"
measurement can be "number" or "percentage"

if I make country the "this one"
and choose "population 50+" for the indicator
and choose "number" for the measurement

The code would graph the chart for each of the countries for the
"population 50+" indicator and measurment "number"?

Thanks for the clarification.

Marina



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

 

[Tom Ogilvy]

Yes. It should do that.

 

[marina madeleine]

Tom and Marina,

Many thanks for the suggestions on everything. For clarity purposes, is
it possible to add something to the VB code for the print pivot chart
macro which puts the following words on the right hand corner of every
chart page printed:

PRELIMINARY INFORMATION
STILL UNDERGOING FINALIZATION

Also, I noticed in the chart pages printed, the page fields in the upper
left corner are being printed straight across instead of vertically.
Would it be possible to adjust the macro so that this is printed
vertically, for example:

instead of like this:
country Austria indicator population measurement number

to make it like this:
country: Austria
indicator: population
measurement: number

Many thanks.

Marina



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

 

[SPIRONIK]

Hello, to everyone,
just because I am new to vba,
about the above code for printing pivot chart, for each item in th
page field,
could we create separate sheets for each item in page field with shee
name as of page field?
How should we write the code then?
Thanks a lo