VBA Copy/Paste Multiple Excel Ranges To PowerPoint Slides
Stop Manually Copying and Pasting!
This post is a continuation of the current most popular blog post on The Spreadsheet Guru website. This post was entitled Copy & Paste An Excel Range Into PowerPoint With VBA. A ton of readers emailed me and asked for guidance on how to copy multiple Excel ranges across multiple PowerPoint slides.
Instead of answering everyone’s questions individually, I thought I would put a quick post together to show how you can turn the VBA macro code provided in the aforementioned post into a mean green transferring machine! Once again I will give you the whole VBA subroutine first and highlight the few tweaks made to the code. Enjoy!
Sub PasteMultipleSlides()
'PURPOSE: Copy Excel Ranges and Paste them into the Active PowerPoint presentation slides
'SOURCE: www.TheSpreadsheetGuru.com
Dim myPresentation As Object
Dim mySlide As Object
Dim PowerPointApp As Object
Dim shp As Object
Dim MySlideArray As Variant
Dim MyRangeArray As Variant
Dim x As Long
'Create an Instance of PowerPoint
On Error Resume Next
'Is PowerPoint already opened?
Set PowerPointApp = GetObject(class:="PowerPoint.Application")
'Clear the error between errors
Err.Clear
'If PowerPoint is not already open then Exit
If PowerPointApp Is Nothing Then
MsgBox "PowerPoint Presentation is not open, aborting."
Exit Sub
End If
'Handle if the PowerPoint Application is not found
If Err.Number = 429 Then
MsgBox "PowerPoint could not be found, aborting."
Exit Sub
End If
On Error GoTo 0
'Make PowerPoint Visible and Active
PowerPointApp.ActiveWindow.Panes(2).Activate
'Create a New Presentation
Set myPresentation = PowerPointApp.ActivePresentation
'List of PPT Slides to Paste to
MySlideArray = Array(2, 3, 4, 5, 6)
'List of Excel Ranges to Copy from
MyRangeArray = Array(Sheet1.Range("A1:C10"), Sheet4.Range("A1:C10"), _
Sheet3.Range("A1:C10"), Sheet2.Range("A1:C10"), Sheet5.Range("A1:C10"))
'Loop through Array data
For x = LBound(MySlideArray) To UBound(MySlideArray)
'Copy Excel Range
MyRangeArray(x).Copy
'Paste to PowerPoint and position
On Error Resume Next
Set shp = myPresentation.Slides(MySlideArray(x)).Shapes.PasteSpecial(DataType:=2) 'Excel 2007-2010
Set shp = PowerPointApp.ActiveWindow.Selection.ShapeRange 'Excel 2013
On Error GoTo 0
'Center Object
With myPresentation.PageSetup
shp.Left = (.SlideWidth \ 2) - (shp.Width \ 2)
shp.Top = (.SlideHeight \ 2) - (shp.Height \ 2)
End With
Next x
'Transfer Complete
Application.CutCopyMode = False
ThisWorkbook.Activate
MsgBox "Complete!"
End SubUsing Arrays To Your Advantage
'List of PPT Slides to Paste to
MySlideArray = Array(2, 3, 4, 5, 6)
'List of Excel Ranges to Copy from
MyRangeArray = Array(Sheet1.Range("A1:C10"), Sheet4.Range("A1:C10"), _
Sheet3.Range("A1:C10"), Sheet2.Range("A1:C10"), Sheet5.Range("A1:C10"))I often like to refer to one-dimensional arrays as lists and they are incredibly useful when automating routine tasks such as copying & pasting data. In the code above you will see two array lists. One will be used to store which Excel ranges you want to copy and the other will store which PowerPoint slides to paste to. When these arrays are perfectly aligned (ie the second range array item belongs with the second slide array item), the real automation begins.
As you can see in the above code snippet, I am referencing ranges in different worksheets, and if you are really up for the challenge you can even reference and pull in data from different Excel workbooks!
Looping Through The Array Content
'Loop through Array data
For x = LBound(MySlideArray) To UBound(MySlideArray)
'Copy Excel Range
MyRangeArray(x).Copy
'Paste to PowerPoint and position
On Error Resume Next
Set shp = myPresentation.Slides(MySlideArray(x)).Shapes.PasteSpecial(DataType:=2) 'Excel 07-10
Set shp = PowerPointApp.ActiveWindow.Selection.ShapeRange 'Excel 2013
On Error GoTo 0
Next xThis snippet of code ensures that you are looping through every single piece of data inside your arrays. By using the LBound (lower bound) and UBound (upper bound) functions, you can ensure that your loop statement cycle through the proper amount of times. If you ever need to increase or decrease your array list (ie need to copy and paste more or fewer amounts of data into your slides), this part of the code will never need to be changed because it is dynamically structured!
NOTE: There seems to be some sort of bug when trying to set the shp variable to the recently pasted image. In Office 2007 and 2010 you can set the variable on the same line as the PasteSpecial. This however causes an error in Office 2013. Likewise, in 2013 you can set a ShapeRange variable equal to the selected shape (a newly pasted shape is automatically selected), however, in 2007/2010 you will receive an error. My “fix” was to try and execute both lines of code and ignore whichever line errors out. If anyone has a better way of handling this or knows why this is happening please leave a comment so we can post the best solution in the article!
Center Image To Middle Of The Slide
'Set variable equal to newly pasted shape
Set shp = PowerPointApp.ActiveWindow.Selection.ShapeRange
'Center Object
With myPresentation.PageSetup
shp.Left = (.SlideWidth \ 2) - (shp.Width \ 2)
shp.Top = (.SlideHeight \ 2) - (shp.Height \ 2)
End WithI pulled this snippet from my Center Shapes on Slide article. The calculations use the height and width of both the pasted image and the PowerPoint slide to find the exact center. By changing the pasted image’s Left and Top positions, every image will be right in the middle of the slide.
Of course, if you have specific locations on the slide for your images you can spell those out by using the Left, Top, Height, and Width properties. Use the VBA snippet from the Code Vault post Retrieve the Dimensions of a Shape in PowerPoint to determine what those values should be.
Also if you are changing the image’s size ratio, make sure to Unlock an Image’s Aspect Ratio, or else you will not get your desired image size.
Never Copy & Paste Into PowerPoint Again!
And there you have it, you now know how to automate the creation of your PowerPoint slides! So what are you going to do with all your extra saved time? Leave a comment below and let everyone know what you are now able to do since you don’t have to sit in front of your computer and copy/paste data all day! I look forward to reading your comments (feel free to be witty)!
Also, I now have an add-in that will do this process for you (and much more!). Check it out by clicking the banner –>
Download Example Excel File
If you would like to get a copy of the Excel file I used throughout this article, feel free to directly download the spreadsheet by clicking the download button below.
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.