Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
pivot table chart/graphing
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
pivot table chart/graphing
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 '============================= marina madeleine wrote: 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 Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
pivot table chart/graphing
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/groups?thre...GP09.phx .gbl For examination, It can write the combinations to sheet3 rather than printing by setting the printflag. -- Regards, Tom Ogilvy "Debra Dalgleish" wrote in message ... 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 '============================= marina madeleine wrote: 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 Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
pivot table chart/graphing
Thanks for the link, Tom. I had missed your original posting of that code.
Tom Ogilvy wrote: 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/groups?thre...GP09.phx .gbl For examination, It can write the combinations to sheet3 rather than printing by setting the printflag. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
pivot table chart/graphing
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
pivot table chart/graphing
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
pivot table chart/graphing
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. -- Regards, Tom Ogilvy "marina madeleine" wrote in message ... 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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
pivot table chart/graphing
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
pivot table chart/graphing
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! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
pivot table chart/graphing
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. -- Regards, Tom Ogilvy "marina madeleine" wrote in message ... 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! |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
pivot table chart/graphing
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 we 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! |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
pivot table chart/graphing
Yes. It should do that.
-- Regards, Tom Ogilvy "marina madeleine" wrote in message ... 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 we 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! |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
pivot table chart/graphing
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! |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
pivot table chart/graphing
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 -- SPIRONI ----------------------------------------------------------------------- SPIRONIK's Profile: http://www.excelforum.com/member.php...fo&userid=1243 View this thread: http://www.excelforum.com/showthread.php?threadid=25731 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Chart Graphing Wrong Data | Charts and Charting in Excel | |||
How to create chart from Pivot table but not Pivot chart? | Charts and Charting in Excel | |||
When refreshing pivot tables my pivot table chart type changes | Excel Discussion (Misc queries) | |||
Pivot Table Chart - With Pivot Table Totals | Charts and Charting in Excel | |||
Pivot Chart Graphing error | Charts and Charting in Excel |