Copy ranges from multiple worksheets in excel and then paste special in Powerpoint

hi, hopefully someone can help me.... Im new to vba and have found some macros that will open powerpoint from excel and others that will copy and paste from excel to powerpoint, but Id like to be able to open Powerpoint template from Excel, copy worksheet data range A1 to say M20, and then paste special into the new powerpoint. Since I have about 50 work sheets with separate data ranges, id like it to cycle to pick up sequentially each sheet and paste in PPoint on a new slide.....

Is this even possible?

i've tried this link but I get errors when using it.

http://vbaexpress.com/kb/getarticle.php?kb_id=370

Hi, sorry for my late response! Here is what I have so far, but i get an error at the Copy Range line where it says " sub or Function Not Defined" .... I'm guessing its my syntax thats incorrect. Here is the code-

Sub ExcelToNewPowerPoint()
Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide

' Create instance of PowerPoint
Set PPApp = CreateObject("Powerpoint.Application")

' For automation to work, PowerPoint must be visible
' (alternatively, other extraordinary measures must be taken)
PPApp.Visible = True

' Create a presentation
Set PPPres = PPApp.Presentations.Add

' Some PowerPoint actions work best in normal slide view
PPApp.ActiveWindow.ViewType = ppViewSlide

' Add first slide to presentation
Set PPSlide = PPPres.Slides.Add(1, ppLayoutTitleOnly)


Dim i As Integer

For i = 1 To ThisWorkbook.Sheets.Count

Sheets(i).Activate

Copy xlRange
xlValue = relative
'
ActiveCell.Range("A1:P35").Select
Selection.Copy

GoTo ppt
Selection = ppt
Paste special
Insert New Slide
GoTo msoslide

GoTo Excel
Next i


End

Hi,

So here is what I have. It works so far in that it opens PowerPoint and then pastes the desired range. The issue is it then stops at Worksheet Tab 12, and I get an error. It also pastes it in the far left hadn corner of the slide, but thats just a positioning format issue. If you know how to fix the cycle it would be great!


Sub ExcelToNewPowerPoint()

Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide

' Create instance of PowerPoint
Set PPApp = CreateObject("Powerpoint.Application")

' For automation to work, PowerPoint must be visible
' (alternatively, other extraordinary measures must be taken)
PPApp.Visible = True

' Create a presentation
Set PPPres = PPApp.Presentations.Add

' Some PowerPoint actions work best in normal slide view
PPApp.ActiveWindow.ViewType = ppViewSlide

' Add first slide to presentation
Set PPSlide = PPPres.Slides.Add(1, ppLayoutBlank)

'GoTo Excel


Dim i As Integer

For i = 1 To ThisWorkbook.Sheets.Count

Sheets(i).Activate


'
Range("A1:P35").Select
'Selection.Copy
Selection.CopyPicture Appearance:=xlScreen, _
Format:=xlPicture


'ActivePresentation.Slides(i).Select
PPApp.Activate
PPApp.ActivePresentation.Slides(i).Shapes.PasteSpecial ppPasteEnhancedMetafile
'PPApp.ActivePresentation.Slides(i).Shapes.Paste


' GoTo Excel
Set PPSlide = PPPres.Slides.Add(i + 1, ppLayoutBlank)
Next i

PPApp.Activate

End Sub

Oppsies my apologies. It seems to run fine, but I do get an error at

Selection.CopyPicture Appearance:=xlScreen, _
Format:=xlPicture

Once pressing play again on the macro it runs the operation fine. Not sure why it hangs there though.

Awesome thank you! That does stop it from hanging.

I have a another question....if you know quick what I need to do.....

I need to center the pasted chart so its not floating in the top left corner. Do I have to run a separate sub for this, or can I insert the formatting after I paste it in during the loop For i = 1 To ThisWorkbook.Sheets.Count
?

Hi, thats ok.


Im not sure how to run multiple functions in these loops.

It craps out, I think it wants me to run separate loops for each action or something. It WILL NOT select the shapes once they are pasted on the page, for some reason it hates them.

Thanks again for your help!