Copy & Paste An Excel Range Into PowerPoint With VBA

Copy & Paste Excel Range to PowerPoint Slide VBA Macro

Why Do This?

I've received a large number of questions on how to command other programs with VBA through Excel.  In this post we will walk through how to copy a specified cell range and paste it into a brand new PowerPoint presentation.  This might seem like a task that would be much more efficient to carry out manually.  However, when you need to put together a presentation with 50+ Excel images, having a VBA macro do all the heavy lifting is much faster!  Let me first show you the VBA code and then I will highlight some of the main sections that are most important.

 

Sub ExcelRangeToPowerPoint()
'PURPOSE: Copy/Paste An Excel Range Into a New PowerPoint Presentation
'SOURCE: www.TheSpreadsheetGuru.com

Dim rng As Range
Dim PowerPointApp As Object
Dim myPresentation As Object
Dim mySlide As Object
Dim myShape As Object

'Copy Range from Excel
  Set rng = ThisWorkbook.ActiveSheet.Range("A1:C12")

'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 open PowerPoint
      If PowerPointApp Is Nothing Then Set PowerPointApp = CreateObject(class:="PowerPoint.Application")
    
    '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

'Optimize Code
  Application.ScreenUpdating = False
  
'Create a New Presentation
  Set myPresentation = PowerPointApp.Presentations.Add

'Add a slide to the Presentation
  Set mySlide = myPresentation.Slides.Add(1, 11) '11 = ppLayoutTitleOnly

'Copy Excel Range
  rng.Copy

'Paste to PowerPoint and position
  mySlide.Shapes.PasteSpecial DataType:=2  '2 = ppPasteEnhancedMetafile
  Set myShape = mySlide.Shapes(mySlide.Shapes.Count)
  
    'Set position:
      myShape.Left = 66
      myShape.Top = 152

'Make PowerPoint Visible and Active
  PowerPointApp.Visible = True
  PowerPointApp.Activate

'Clear The Clipboard
  Application.CutCopyMode = False
  
End Sub

Teaching Excel The PowerPoint Language

In order to control PowerPoint from inside Excel's Visual Basic Editor, we need to teach Excel how to speak in PowerPoint's terms.  PowerPoint uses a vocabulary containing terms such as "Slide" and "Presentation" that simply do not exist in Excel's own code language.  You can learn more about this topic by reading my post Teaching Excel To Speak PowerPoint.  In order to activate PowerPoint's object library, simply click the Tools menu in the Visual Basic Editor and select Reference.  Then make sure the reference Microsoft Office PowerPoint 12.0 Object Library is checked (the version number may verify, for example in Office 2016 the reference would be Office PowerPoint 16.0 Object Library).  In the below image I show you exactly how to do this and prove afterwards that Excel now knows PowerPoint lingo!

UPDATE: I have updated the code in this article so you do not need to do this step, however intellisense for any PowerPoint objects in the Visual Basic Editor will not work. This is not that big of a deal unless you need coding direction adding additional PowerPoint automation to your VBA macro.

Opening PowerPoint From Inside Excel

Now that we have taught Excel how to communicate with PowerPoint, let's look at how we can accomplish pasting a range into a brand new slide.  Near the beginning of the code there is a section that looks like this:

'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 open PowerPoint
      If PowerPointApp Is Nothing Then Set PowerPointApp = CreateObject(class:="PowerPoint.Application")
    
    '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

In this part of the code we are determining if Microsoft PowerPoint is open or not.  If PowerPoint is already open, we can set a variable equal to the entire program by using GetObject.  If PowerPoint is not currently running we can use CreateObject to run an instance of PowerPoint and then set a variable equal to that specific instance of PowerPoint.

When using CreateObject, the target application will start running but it is not visible on screen.  Therefore we need to turn the Visible setting on (equal to true).  Also, VBA with PowerPoint is a little bit different than with Excel in that it is much more dependent on its window showing on screen.  Therefore a second command must be written to Activate PowerPoint.

'Make PowerPoint Visible and Active
  PowerPointApp.Visible = True
  PowerPointApp.Activate

Controlling PowerPoint From Inside Excel

Now that you are 100% sure that PowerPoint is running, you can begin to control it!  Let's first command PowerPoint to make a brand new presentation and then create a slide to paste your range to.  Along the way it is important to set a variable during each stage of creation so that if you ever want to reference the presentation or a newly created slide you can do so with your respective variable.

'Create a New Presentation
  Set myPresentation = PowerPointApp.Presentations.Add

'Add a slide to the Presentation
  Set mySlide = myPresentation.Slides.Add(1, 11) '11 = ppLayoutTitleOnly

Copy From Excel, Paste Onto Slide

Now that you have a new presentation and a new slide, you can command Excel to paste your range into PowerPoint.  Near the beginning of the code, there was a line that allowed you to specify the exact range you wanted to copy.  The variable rng was used to remember this range and to allow you to reference the range later on in the code.  

Guru Tip: It is a good idea to place code that may need to be manually changed at some point in the future near the beginning of the subroutine.  This prevents you from having to scroll through your code and pinpoint the exact place where you spelled out which range you wanted to copy or which worksheet you wanted to pull data from.  This can save you a bunch of time and prevent confusion!

'Copy Range from Excel
  Set rng = ThisWorkbook.ActiveSheet.Range("A1:D12")

'Copy Excel Range
  rng.Copy

'Paste to PowerPoint and position
  Set mySlide = myPresentation.Slides.Add(1, 11) '11 = ppLayoutTitleOnly
  Set myShapeRange = mySlide.Shapes(mySlide.Shapes.Count)

You can then use PasteSpecial to determine how you want to paste your specific range.  I typically use paste as Enhanced Metafile, but below are the possible paste types that you can use in its place:

 
Paste TypeEnumVBA Code
Bitmap1ppPasteBitmap
Default0ppPasteDefault
Enhanced Metafile2ppPasteEnhancedMetafile
GIF4ppPasteGIF
HTML8ppPasteHTML
JPG5ppPasteJPG
Metafile Picture3ppPasteMetafilePicture
OLE Object10ppPasteOLEObject
PNG6ppPastePNG
RTF9ppPasteRTF
Shape11ppPasteShape
Text7ppPasteText
 

Notice how we set a variable equal to the newly pasted item.  This is important because it allows you to modify the picture after it has been placed into PowerPoint.  In the example code, I chose to change the picture position by setting the Left and Top dimensions.  You could also choose to resize the image, add a shadow, or to apply any other modification you would typically do manually inside of PowerPoint.  

'Set position:
  myShapeRange.Left = 234
  myShapeRange.Top = 186

You have now reached the end of the macro and successfully pasted in a range from Excel into a PowerPoint presentation!

Learn What VBA Can Do With PowerPoint

For the longest time I had no idea I could use VBA with PowerPoint.  I thought VBA was strictly an Excel thing.  It was actually through an online VBA course that I was shown how to control PowerPoint & Word with VBA commands.  It's unfortunate that there is very little documentation out on the web concerning VBA outside of Excel.  It is one of the major contributors in personally deciding to create this site and I hope to compile a bunch of resources for you in these areas.  The best place to get your feet wet with VBA for PowerPoint is to hop on over to the PowerPoint Code Vault.  This is where I post pre-written code and it serves as a great guide on how to write VBA for PowerPoint.

Also, if you want to see this code in action inside a live Excel worksheet.  Click the download button below to have an Excel example file instantly sent to your email inbox. 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 Can You Use These Concepts?

I can see all the light bulbs flashing around your head and there really are a ton of useful ways to take the concepts laid out in this post and apply them to really automate how you interact between Excel and PowerPoint.  I personally have a bunch of my own spreadsheets set up so that I can run a macro that creates 25 slides automatically within seconds!  Leave a comment below and let me know if you now plan to automate how you create your presentations!  If you already use macros to provide automation between PowerPoint and Excel, what does your VBA do?  I look forward to hearing your response!

Posts You Might Also Be Interested In...

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

Center An Object On Your Slide

Resize An Oversized Pasted PowerPoint Image

Retrieve The Dimensions Of A Shape In PowerPoint

A brand new Excel add-in that will automate creating PowerPoint slides from your Excel data! Click the image to learn more! #FreeTrial

A brand new Excel add-in that will automate creating PowerPoint slides from your Excel data! Click the image to learn more! #FreeTrial

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 :)