I'm creating a macro that will take screenshots of specific ranges within 5 Excel tabs, and export them into specific slides on an existing PowerPoint located on my desktop. For some reason, the macro worked, and then a few days later it isn't anymore. With no edits done to the PowerPoint/Excel/etc.
It is still opening the PowerPoint correctly, and copying the range as needed. The failure comes when pasting, it highlights "pptSlide.Shapes.PasteSpecial" with a runtime error that states clipboard is empty or contains data which may not be pasted here. I've already confirmed that the clipboard is not empty, and tried adding a Wait/Sleep command between the copy and paste to make sure the macro isn't moving too fast, to no avail. I tried defining the data type, which did not do anything. I want to emphasize that this code worked exactly as is a few days ago.
Here is my code with red lettering for the highlighted error:
Sub PrintScreenshotsToPowerPoint()
Dim pptApp As Object
Dim pptPres As Object
Dim pptSlide As Object
Dim ws As Worksheet
Dim rng As Range
Dim imgPath As String
' Open PowerPoint presentation
Set pptApp = CreateObject("PowerPoint.Application")
pptApp.Visible = True
Set pptPres = pptApp.Presentations.Open(Environ$("USERPROFILE") & "\Desktop\Investment Proposal\Investment Proposal.pptx")
' Take screenshot of range A1:M36 on "Ast Alloc" sheet
Set ws = ThisWorkbook.Sheets("Ast Alloc")
Set rng = ws.Range("A1:M36")
rng.CopyPicture Appearance:=xlPrinter, Format:=xlPicture
' Paste screenshot into slide 19
Set pptSlide = pptPres.Slides(19)
**pptSlide.Shapes.PasteSpecial**
With pptSlide.Shapes(pptSlide.Shapes.Count)
.LockAspectRatio = False
.Top = 95
.Left = 22
.Height = 500
End With
' Take screenshot of range A1:M30 on "NQ v Q" sheet
Set ws = ThisWorkbook.Sheets("NQ v Q")
Set rng = ws.Range("A1:M30")
rng.CopyPicture Appearance:=xlScreen, Format:=xlPicture
' Paste screenshot into slide 20
Set pptSlide = pptPres.Slides(20)
pptSlide.Shapes.PasteSpecial
With pptSlide.Shapes(pptSlide.Shapes.Count)
.LockAspectRatio = msoTrue
.Top = 100
End With
' Take screenshot of range A1:M50 on "Coordination" sheet
Set ws = ThisWorkbook.Sheets("Coordination")
Set rng = ws.Range("A1:M50")
rng.CopyPicture Appearance:=xlPrinter, Format:=xlPicture
' Paste screenshot into slide 21
Set pptSlide = pptPres.Slides(21)
pptSlide.Shapes.PasteSpecial
With pptSlide.Shapes(pptSlide.Shapes.Count)
.LockAspectRatio = False
.Top = 93
.Left = 21
.Width = 750
End With
' Take screenshot of range A1:J48 on "Est Income" sheet
Set ws = ThisWorkbook.Sheets("Est Income")
Set rng = ws.Range("A1:J48")
rng.CopyPicture Appearance:=xlPrinter, Format:=xlPicture
' Paste screenshot into slide 22
Set pptSlide = pptPres.Slides(22)
pptSlide.Shapes.PasteSpecial
With pptSlide.Shapes(pptSlide.Shapes.Count)
.LockAspectRatio = msoTrue
.Top = 100
End With
' Take screenshot of range A1:J50 on "Fund Info" sheet
Set ws = ThisWorkbook.Sheets("Fund Info")
Set rng = ws.Range("A1:J50")
rng.CopyPicture Appearance:=xlPrinter, Format:=xlPicture
' Paste screenshot into slide 23
Set pptSlide = pptPres.Slides(23)
pptSlide.Shapes.PasteSpecial
With pptSlide.Shapes(pptSlide.Shapes.Count)
.LockAspectRatio = msoTrue
.Top = 100
End With
End Sub