Printing all the charts

[sam]

I am a C/C++/.Net developer. I am working on a basic Excel
spreadsheet for my son's Boy Scout Troop. One sheet contains ALL the
charts that need to be printed weekly. I need to print one chart per
page. I have a funny feeling that it is a quick and easy 'for each'
to print them, but I am guessing. Might someone be able to enlighten
me?

Cartoper

 

[Peter T]

If the charts you want to print are individual Chart-sheets you can do
simply run this:

Charts.PrintOut

above assumes the ActiveWorkbook, qualify if necessary to the required
workbook.

If this is all you are doing I can't see any reason not to use a simple one
line VBA macro.

If your charts are embedded on worksheets could loop each chart on each
worksheet, to place a copy of each into a chart-sheet. Run the code above,
then delete the newly created chart sheets.

If you want your charts sized and/or orientated in some particular way on
the paper there would be a bit more work to do.

Regards,
Peter T

 

[Joel]

The esy way is to put a page break between charts. On the spreadsheet menu
Insert - Page break. I usually put the charts next to each other either
vertically and/or horizontal. Then add the breaks.

 

[Jon Peltier]

If you printout an embedded chart, it is printed as a chart sheet. No need
to manipulate the chart to make a copy as a chart sheet. Here's the code:

Sub PrintAllChartsOnThisWorksheet()
Dim ChtOb As ChartObject
For Each ChtOb In ActiveWorksheet.ChartObjects
ChtOb.Chart.PrintOut
Next
End Sub

- Jon

 

[Peter T]

I think a typo here -

For Each ChtOb In ActiveWorksheet.ChartObjects

should read -
For Each ChtOb In ActiveSheet.ChartObjects

I wasn't aware of that method and handy to know, if ever want to print all
charts on a worksheet to individual pages.

FWIW, the method I suggested would allow for all charts to be printed to
pages as a single print operation, in case say two sided printing is
required. Also, possibly, a bit easier to manipulate page setup: margins,
orientation etc.

Regards,
Peter T

 

[Jon Peltier]

Oops, didn't test it.

The page setup can be accomplished with the chart prior to printing. This
one is even tested!

Sub PrintoutCharts()
Dim chob As ChartObject
For Each chob In ActiveSheet.ChartObjects
With chob.Chart.PageSetup
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
End With
chob.Chart.PrintOut
Next
End Sub

- Jon