×

VBA Copy/Paste Multiple Excel Ranges To PowerPoint Slides

By Chris Newman •  Updated: 06/30/14 •  8 min read
Copy Paste Multiple Excel Ranges To Multiple PowerPoint Slides With VBA

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.  

Stop Manually Copying and Pasting!

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 Sub

Using 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 x

This 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 With

I 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 Ratioor 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 -->

Never Copy & Paste Into PowerPoint Again!

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.

Keep Learning

Chris Newman

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.