Printing Multiple Charts as One Print Job in Excel 2003 VBA

OK, neither of these are "clean" enough solutions for me as they require adding extra worksheets, etc.  Thanks for answering though.  

I have found what I think is actually a much better solution that accomplishes 2 things:
1) send the files to Powerpoint - this works amazingly fast!
2) then print from Powerpoint IF desired

The REALLY nice thing about this solution is that in many cases users don't want to print out anyway but really what to send the charts electronically.  Instead of printing to PDF as we've done in the past we can create Powerpoint slides which can also be fit right into presentations.  This works REALLY slick(ly).

This is a simplification of a solution I found on the internet (I kept the original credit in the code).  Note that the Powerpoint Object Library must be checked under Visual Basic  Tools -> References and there must be an open powerpoint presentation for this to work.  I added a little error catch in case a powerpoint presentation is not open.  Good also to warn users that whatever is their current open powerpoint will be appended.

Function SingleChartToPresentation() As Integer
' Set a VBE reference to Microsoft PowerPoint Object Library
' This is a modification of code written by Jon Peltier found at
' http://peltiertech.com/Excel/XL_PPT.html
' the modification was made to work with a chart on a chart sheet instead of
' a chart in a worksheet. In addition, the code was changed to restore the
' title font size back to its original size instead of the default.
 
' Modified by Frank Hayes
' 2nd modification by David Glasscock to simplify and add error catch
 
Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim PresentationFileName As Variant
Dim SlideCount As Long
Dim iCht As Integer
Dim sTitle
Dim sTitleSize As Integer
 
SingleChartToPresentation = 0
On Error GoTo 100
' Reference existing instance of PowerPoint
Set PPApp = GetObject(, "Powerpoint.Application")
' Reference active presentation
 
Set PPPres = PPApp.ActivePresentation
PPApp.ActiveWindow.ViewType = ppViewSlide
 
With ActiveChart
 
' copy chart as a picture
.CopyPicture _
Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture
 
 
End With
 
' Add a new slide and paste in the chart
SlideCount = PPPres.Slides.Count
Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutBlank)
 
PPApp.ActiveWindow.View.GotoSlide PPSlide.SlideIndex
With PPSlide
' paste and select the chart picture
.Shapes.Paste.Select
' align the chart
PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
End With
 
'MsgBox "Chart Copied To PowerPoint Presentation"
 
iCht = (Application.WorksheetFunction.MAX((SlideCount - ChartsAdded + 2), 1))
PPApp.ActiveWindow.View.GotoSlide (iCht)
 
' Clean up
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing
Exit Function
 
100 MsgBox ("Open Powerpoint to a new presentation then add charts")
SingleChartToPresentation = -1
Exit Function
 
 
End Function

Select allOpen in new window

my comment on 4/28/08 worked as the solution.