Copy and Paste from Excel to PowerPoint using VBA

Question

I have specific columns' names in Excel that I want to copy and paste into PowerPoint but I can't run the code because I get "Run Error 424." I have tried using ("B3:Q3") for the columns and that works. However, I don't want all those columns, I only want the columns that are listed below ("b3,f3,l3,n3,p3,q3"). Can anyone assist? Thank you so much!

Sub ExcelRangeToPowerPoint()

Dim rng As Range
Dim rng1 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("b3,f3,l3,n3,p3,q3").Select 'THIS IS THE ERROR
  Set rng1 = ThisWorkbook.ActiveSheet.Range("G4:I4")

'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:=7  '7 = ppPasteText
  Set myShape = mySlide.Shapes(mySlide.Shapes.Count)

'Set position:rng
  myShape.Left = 70
  myShape.Top = 150
  myShape.Width = 800
  myShape.Height = 100

'Copy Excel Range
  rng1.Copy

'Paste to PowerPoint and position
  mySlide.Shapes.PasteSpecial DataType:=7  '7 = ppPasteText
  Set myShape = mySlide.Shapes(mySlide.Shapes.Count)

'Set position:rng
  myShape.Left = 70
  myShape.Top = 200
  myShape.Width = 800
  myShape.Height = 300

'Insert the tile on the ppt
mySlide.Shapes.Title.TextFrame.TextRange.Text = "Insert Title Here"

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

'Clear The Clipboard
  Application.CutCopyMode = False

End Sub

BruceWayne · Accepted Answer · 2019-03-18T17:39:40.910

0

Take off the .Select.

1) You can't Set Rng = [whatever].Select. You want to do Set Rng = [whatever] then Rng.Select on a new line, but more importantly,

2) It's best to Avoid using .Select/.Activate. Although you don't seem to use it elsewhere (good!), so I bet this is just a "typo".

Also, if you want the Columns then you would do:

Set rng = ThisWorkbook.ActiveSheet.Range("b3,f3,l3,n3,p3,q3").EntireColumn

Edit: This won't solve the issue of it pasting the in-between columns, but this (admittedly a little klunky) code will select just the data used (including headers), instead of the entire columns:

 'Copy Range from Excel
  Dim lastRow As Long
  With ThisWorkbook.ActiveSheet

    lastRow = .Cells(Rows.Count, 2).End(xlUp).Row
    ' I assume your headers actually are in row 3, and the data is in row 4 on ward:
    Set rng = ThisWorkbook.ActiveSheet.Range("b3:B" & lastRow & ",f3:F" & lastRow & ",l3:l" & lastRow & ",n3:N" & lastRow & ",p3:P" & lastRow & ",q3:Q" & lastRow)
    Set rng1 = ThisWorkbook.ActiveSheet.Range("G4:I4")
  End With
 'Create an Instance of PowerPoint
  On Error Resume Next
 ' Etc. etc.

edited Mar 18 '19 at 17:39

answered Mar 18 '19 at 16:26

BruceWayne

22,923
15
65
110

Hi Bruce! Thank you SO much for the fast answer.! This worked! I just took off the ".Select" and I don't have more errors. However, it pasted all the columns from ("b3:q3") even though I'm selecting specific columns ("b3,f3,l3,n3,p3,q3"). Do you know why is this? – Nicole P. Mar 18 '19 at 16:37
@NicoleP. - I'm not too familiar with the VBA in PowerPoint - but where in the code are you pasting the columns? Is it here, `mySlide.Shapes.PasteSpecial DataType:=7`? – BruceWayne Mar 18 '19 at 16:39
Yes! to that slide. – Nicole P. Mar 18 '19 at 16:39
@NicoleP. - Hmmm, that's odd. Not really related to *that* issue, but I doubt you really need/want to copy the *entire column*, right? Do you have the same amount of data in each column (is the data all the same number of rows), or does one row have more info than others? – BruceWayne Mar 18 '19 at 17:00
that's correct Bruce. I don't want to copy the "entire column" I only want the titles of those columns ( so only one row, row3). I do have the same amount of data in each column. All the rows have the same amount of data. The data is text. I don't have any numbers in my cells. For the entire scope of my code. I'm trying to only copy the titles of my excel table on the slide first, and then I'm going to write a code that loops through every row and paste the data under the titles. I'm definitely a little more familiar with Excel VBA but PowerPoint is making it more difficult. – Nicole P. Mar 18 '19 at 17:16
@NicoleP. - I'm with you on that (more familiar with Excel than PowerPoint). - This is beyond the scope of this question, but maybe we can do this: You just want the HEADER (i.e. first row of `Rng`) on one slide, then on the following slide, show the headers + data? (Instead of pasting the values, would inserting the Excel data as a table work?) – BruceWayne Mar 18 '19 at 17:18
Yes I know, that approach is definitely way more easier because I would just paste the table with all the content but unfortunately the ppt presentation has to be in a certain format and it cant be just the table. That's why I'm pasting it as a text and editing the formatting it later on the code. But thank you so much for all your help Bruce :D! you did answered my question correctly though regardless! – Nicole P. Mar 18 '19 at 17:22
@NicoleP. - See my edit on how you can cut down the `Rng` to what's used. (I assume column B has the "most" data (though you said all columns have same data, I had to choose some column to use, so just went with B). I'd ask a new question on why the PowerPoint `Paste` is including the inbetween columns too. – BruceWayne Mar 18 '19 at 17:40
@NicoleP. - OOH! See [this thread](https://superuser.com/questions/709794/changing-the-bounds-of-an-inserted-excel-spreadsheet-in-a-powerpoint-slide). It seems that hiding the inbetween columns will do the trick. – BruceWayne Mar 18 '19 at 17:43
Thank you so much for you edits! those are very helpful. I was able to paste the whole table from row 3 until whatever the last row is. I also saw the link you provided and that helped with formatting! – Nicole P. Mar 18 '19 at 20:42

Copy and Paste from Excel to PowerPoint using VBA

1 Answers1