Copy & Paste Multiple Excel Ranges To Separate PowerPoint Slides With VBA

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.  Instead of answering everyone's questions individual, 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 task such as copy & 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 paste more or less 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 an execute both lines of code and ignore which ever 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 a post in the PowerPoint Code Vault.  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 The Example Files

Click the link below if you would like to play around with the files I used to create the GIF image located at the beginning of the post.  Hopefully, it will give you another perspective on how the VBA code works. 

As always, in order to download this article's example file you will need to be a subscriber of my free tips newsletter. If you click the green button below you can easily sign up and you will be emailed the password to get into the subscribers-only area of this website.

 
                   Already Subscribed? Click HERE to log-in to the "Example Files" section

                   Already Subscribed? Click HERE to log-in to the "Example Files" section

 

How Do I Modify This To Fit My Specific Needs?

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it

I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

Chris "Macro" Newman :)