Share via

VBA Code failing to paste from Excel to PowerPoint

Anonymous
2024-03-22T17:05:50+00:00

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

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

3 answers

Sort by: Most helpful
  1. Anonymous
    2024-08-01T04:29:05+00:00

    Hi,

    replace

    ' 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

    ===================

    with this

    ' Take screenshot of range A1:M36 on "Ast Alloc" sheet

    Set ws = ThisWorkbook.Sheets("Ast Alloc")

    Set rng = ws.Range("A1:M36")

    Set pptSlide = pptPres.Slides(19)

    'delete old shape1/ from slide19

    pptSlide.Shapes(1).Delete

    '

    rng.Copy

    pptSlide.Shapes.PasteSpecial DataType:=2

    Application.CutCopyMode = False

    '

    With pptSlide.Shapes(1)

    .LockAspectRatio = False

    .Top = 10 '95

    .Left = 10 '22

    .Height = 500

    End With

    Was this answer helpful?

    0 comments No comments
  2. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.


    Comments have been turned off. Learn more

  3. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.


    Comments have been turned off. Learn more