Outstanding!! Thank you, Jon!

For those of us who fight the IT battle alone you guys are a lifeline.

Mitch

"Jon Peltier" wrote:

The code you've posted copies all chart objects embedded in the active sheet
to a new PowerPoint slide.

To change to copying chart sheets, replace these lines

For iCht = 1 To ActiveSheet.ChartObjects.Count
' copy chart as a picture
ActiveSheet.ChartObjects(iCht).Chart.CopyPicture _
Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture

with these lines

For iCht = 1 To ActiveWorkbook.Charts.Count
' copy chart as a picture
ActiveWorkbook.Charts(iCht).CopyPicture _
Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture

This will copy every chart sheet to its own slide.

If you have one chart embedded in each worksheet, you can try this
variation:

For iCht = 1 To ActiveWorkbook.Worksheets.Count
' copy chart as a picture
ActiveWorkbook.Worksheets(iCht).ChartObjects(1).Ch art.CopyPicture _
Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"My Own IT dept" wrote in message
...
Jon,

First let me say that I am a complete novice when it comes to using VBE.
I
feel as if I'm in another counrty, don't understand the language and am
using
hand gestures and grunting to try to get my problem solved. I'm missing
some
basic understanding of how this whole system works. That said...

I'm trying to copy multiple charts (1 per worksheet) from a range of
worksheets.
I highlight the range of WS tabs and run the macro (F5). I get no error
message but nothing happens (no charts copied). I have a PPT document
open
with more than enough blank pages (although I don't think that's necessary
based on what I think I read in the code).

Is there something else I should be doing?

Here is the code that I entered into the VBE editor (should look very
familiar):

Sub ChartstoPresentation()
' Set a VBE reference to Microsoft PowerPoint Object Library

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

' Reference existing instance of PowerPoint
Set PPApp = GetObject(, "Powerpoint.Application")
' Reference active presentation
Set PPPres = PPApp.ActivePresentation
PPApp.ActiveWindow.ViewType = ppViewSlide

For iCht = 1 To ActiveSheet.ChartObjects.Count
' copy chart as a picture
ActiveSheet.ChartObjects(iCht).Chart.CopyPicture _
Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture

' 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
' position the chart
With PPApp.ActiveWindow.Selection.ShapeRange
.Top = 94 ' points
.Left = 58 ' points
.Width = 8.2 * 72
.Height = 5.6 * 72
End With
End With

Next

' Clean up
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing

End Sub

-----

Any suggestions?

Thanks very much.


"Jon Peltier" wrote:

I don't recall now, and if I recalled then I'd probably have mentioned
it.

To get back to your original question, you could adjust the pasted
object's
size and position:

With PPSlide
' paste and select the chart picture
.Shapes.Paste.Select
' align the chart
With PPApp.ActiveWindow.Selection.ShapeRange.
.Top = [some value]
.Left = [some value]
.Height = [some value]
.Width = [some value]
End With


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"My Own IT dept" wrote in message
...
Jon,

I've tried to find that other forum in which you said how to set the
desired
chart size right in Excel, but can't. Please let me know which one it
is.

Thanks.


"Jon Peltier" wrote:

In a different forum I answered one way, that you should make it in
Excel
so
that it comes out right in PowerPoint. I stick by that, but I'll
answer
the
other way as well. You can adjust this part of the code to handle
resizing
of the chart.

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

Instead of centering the chart, position and resize it:

With PPSlide
' paste and select the chart picture
.Shapes.Paste.Select
' position the chart
With PPApp.ActiveWindow.Selection.ShapeRange
.Top = 72 ' points
.Left = 0
.Width = 9.66 * 72
.Height = 5.66 * 72
End With
End With

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"mustang25" wrote in message
...
Jon,

This is working brilliantly. I'd like to throw one more challenge
at
you
if
you don't mind. It follows below. Here is the code I'm using for
the
macro
(you'll notice that I removed the line/command that copies the chart
as
a
picture. This is intentional.):

Sub ChartsToPresentation()
' Set a VBE reference to Microsoft PowerPoint Object Library

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

' Reference existing instance of PowerPoint
Set PPApp = GetObject(, "Powerpoint.Application")
' Reference active presentation
Set PPPres = PPApp.ActivePresentation
PPApp.ActiveWindow.ViewType = ppViewSlide

For iCht = 1 To ActiveSheet.ChartObjects.Count
' copy chart as a picture
ActiveSheet.ChartObjects(iCht).Copy

' 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

Next

' Clean up
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing

End Sub

Is there a way to output the charts to PowerPoint with the following
parameters:

Height 5.66 inches
Width 9.66 inches
Horizontal Position 0 inches from top left corner
Vertical Position 1 inch from top left corner

Many thanks in advance!


"Jon Peltier" wrote:

I suspect you didn't set the reference to PowerPoint. It's
described
higher
on the page than the specific code you must have copied. Just under
the
big
title "Activating Other Applications with Excel VBA".

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

"mustang25" wrote in message
...
It amazes me to see the amount of knowledge stored on these
forums.
I'm
seriously impressed that there is an answer to my question.
Thank
you
Jon.

Unfortunately, my VB skills are at about the same level as my 2