Unable to run Copy and Paste Excel range to Powerpoint VBA in O365

msnovice

New Member
Joined
Oct 6, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I am hoping I can get some assistance with this code. I have found the below code from The Spreadsheet Guru for copying ranges from separate Excel worksheets to separate Powerpoint slides and modified it to suit my needs. The code runs fine on older version of Excel but throws an "Run time error 91: Object variable or With block variable not set" error whenever I try to run it on O365 Excel.

Thank you in advance.

Private Sub CommandButton1_Click()
'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
Dim sheet As Worksheet
Dim newSlide As Slide

'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



For Each sheet In Worksheets
'List of PPT Slides to Paste to
Set newSlide = myPresentation.Slides.Add(Index:=myPresentation.Slides.Count + 1, Layout:=ppLayoutBlank)
MySlideArray = Array(myPresentation.Slides.Count)

'List of Excel Ranges to Copy from
MyRangeArray = Array(sheet.Range("B2:J19"))

'Loop through Array data
For x = LBound(MySlideArray) To UBound(MySlideArray)
'Copy Excel Range
MyRangeArray(x).Copy

'Paste to PowerPoint and position (this where I can't get it to work)
On Error Resume Next
myPresentation.Slides(MySlideArray(x)).Select
Set shp = myPresentation.Slides(MySlideArray(x)).Shapes.PasteSpecial(DataType:=0)(1) 'Excel 2007-2010
'Set shp = PowerPointApp.ActiveWindow.Selection.ShapeRange 'Excel 2013
On Error GoTo 0


'Center Object
With myPresentation.PageSetup
shp.Left = 15
shp.Top = 15
shp.Width = 920
shp.Height = 450
End With
Next x

MyRangeArray = Array(sheet.Range("L4:O15"))

'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
myPresentation.Slides(MySlideArray(x)).Select
Set shp = myPresentation.Slides(MySlideArray(x)).Shapes.PasteSpecial(DataType:=2)(1) 'Excel 2007-2010
'Set shp = PowerPointApp.ActiveWindow.Selection.ShapeRange 'Excel 2013
On Error GoTo 0


'Center Object
With myPresentation.PageSetup
shp.Left = 630
shp.Top = 40
shp.Width = 250
shp.Height = 300
End With


Next x

Next sheet

'Transfer Complete
Application.CutCopyMode = False
ThisWorkbook.Activate
MsgBox "Complete!"
End Sub
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Forum statistics

Threads
1,147,634
Messages
5,742,240
Members
423,717
Latest member
rubthenut

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top