Excel Charts to Powerpoint Paste Special

Cross posted at: http://www.mrexcel.com/forum/showthread.php?t=639669


I used the following http://chandoo.org/wp/2011/08/03/cre...ing-excel-vba/

but then the issue I have is some axis on my interactive dashboard are cells and not part of the chart. So for me the best option would be to paste special into a powerpoint.

So for example chart one takes an area of
=B3:O18

The user can customize the display for 2012, 2011, 2010 etc. So each time the macro is executed, a screen shot of that area will be captured and pasted into a new slide on power point.

Sub CreateGraphicOnSlide()

Dim ppApp As PowerPoint.Application
Dim ppPres As PowerPoint.Presentation
Dim ppShape As PowerPoint.Shape
Dim ppCurrentSlide As PowerPoint.Slide
Dim r As Range, sh As Worksheet

'choose which range you want to use
Set r = ActiveSheet.Range("A1:O18")
'Set r = ActiveSheet.Range("A1:N30")

Worksheets.Add After:=Worksheets(Worksheets.Count)
Set sh = Worksheets(Worksheets.Count)
r.Parent.Activate
r.Select
Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
sh.Pictures.Paste
sh.Shapes(1).Copy

Set ppApp = CreateObject("PowerPoint.Application")
ppApp.Visible = True

Set ppPres = ppApp.Presentations.Add(msoTrue)
Set ppCurrentSlide = ppPres.Slides.Add(Index:=1, Layout:=ppLayoutBlank)


With ppCurrentSlide
ppCurrentSlide.Shapes.Paste
Set ppShp = ppCurrentSlide.Shapes(1)
ppShp.Top = 50
ppShp.Left = 40

' use the following if necessary

' ppShp.width = ??
' ppShp.height = ??


End With

' Save the presentation and quit Microsoft PowerPoint.
'ppPres.SaveAs "c:\My Documents\pptExample2", ppSaveAsPresentation
'ppApp.Quit
Set ppApp = Nothing
Application.DisplayAlerts = False
sh.Delete
Application.DisplayAlerts = True
End Sub

Currently I am using that code. However, I would like it so each time the macor is run.. to input a new slide with the selection.

So the first time it is run.. create powerpoint, then for every following new slide.

R is defined as Set r = ActiveSheet.Range("A1:O18")
but I would like other variables like

Set s = ActiveSheet.Range("B20:R33")
Set t= ActiveSheet.Range("B34:R46")