Looping macro to paste Excel ranges to new Powerpoint slides

**sammonite** · 03-22-2011, 01:10 PM

I found a great starting point from http://peltiertech.com/Excel/XL_PPT.html which "Paste a Selected Excel Worksheet Range into the Active PowerPoint Slide" but it uses one selected range and pastes it to one PowerPoint page.

I want to use multiple Excel ranges and paste each one to a new slide in PowerPoint. For example, I want to create a new PPT, paste Excel range "TableA" to PPT slide 1, then paste Excel range "TableB" to PPT slide 2, etc. Each range would be pasted in the center of the PPT slide.

Here's the starter code from the website above:
******************************************************

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

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

' Make sure a range is selected
If Not TypeName(Selection) = "Range" Then
    MsgBox "Please select a worksheet range and try again.", vbExclamation, _
        "No Range Selected"
Else
    ' Reference existing instance of PowerPoint
    Set PPApp = GetObject(, "Powerpoint.Application")
    ' Reference active presentation
    Set PPPres = PPApp.ActivePresentation
    PPApp.ActiveWindow.ViewType = ppViewSlide
    ' Reference active slide
    Set PPSlide = PPPres.Slides(PPApp.ActiveWindow.Selection.SlideRange.SlideIndex)
    
    ' Copy the range as a piicture
    Selection.CopyPicture Appearance:=xlScreen, _
        Format:=xlPicture

    ' Paste the range
    PPSlide.Shapes.Paste.Select
    
    ' Align the pasted range
    PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
    PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True

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

End Sub

*****************************************************
Never mind. I finally solved this one myself...

**mlexcelhelpforum** · 06-14-2011, 12:10 PM

Hi Salmonite,

Could you share your solution with me?

I'm looking to do something similar, except for I'm not copying charts, but just individual cells, and hopefully each cell to a new powerpoint slide.

Thanks a bunch,
ML

**NKOTB_VBA** · 06-17-2011, 07:15 AM

Yes, I would be interested in that too!! I am trying to do something similar with one of my projects where I have multiple sets of data ranges in excel as well as charts which I want to get into a powerpoint so that I get a skeleton. This is a weekly exercise and I spend about 4 hours every week on it.

**midnightgolfer** · 07-15-2011, 06:56 AM

Yes, do share, please.

**rick_tiger** · 08-06-2012, 04:55 AM

Hi is it possible for you to send me this code as well?

Thanks

**arlu1201** · 08-06-2012, 05:18 AM

rick_tiger,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.