Error when copy pasting a chart through VBA

Question

I have been using this code to copy across a number of ranges and charts. However as my code has grown it appears to fall over, having googled around the issue i think it is caused by the chart/range not correctly being copied to/from the clipboard cache. Is there a way to avoid this error?

Error - " Run-time error '-2147188160 (80048248)': Shapes.PasteSpecial :Invalid request. Clipboard is empty or contains data which may not be pasted here"

Public Sub CopyPasteHeadcountTopGraph()
    If PPT Is Nothing Then Exit Sub
    If PPT_pres Is Nothing Then Exit Sub

    Dim rng As Range
    Dim mySlide As Object
    Dim myShape As Object
    Dim cht As Chart

    Set mySlide = PPT_pres.Slides(6)

    With mySlide
    .Select
    Set cht = ThisWorkbook.Worksheets("Headcount").ChartObjects("HcChart").Chart

       cht.CopyPicture Appearance:=xlScreen, Format:=xlPicture, Size:=xlScreen
       .Shapes.Paste.Select 'ERROR HERE

        '''''''''''''''''''''''''''''''''
        'Paste as Chart and break link. '
        '''''''''''''''''''''''''''''''''
        'cht.ChartArea.Copy
        '.Shapes.Paste.Select


    'With .Shapes("HcChart")
        '.LinkFormat.BreakLink
    'End With

        PPT_pres.Windows(1).Selection.ShapeRange.Left = 35
        PPT_pres.Windows(1).Selection.ShapeRange.Top = 110
        PPT_pres.Windows(1).Selection.ShapeRange.Width = 655
        PPT_pres.Windows(1).Selection.ShapeRange.Height = 300

        End With

    'Clear The Clipboard
    Application.CutCopyMode = False
    Application.Wait (Now + TimeValue("00:00:01"))

End Sub

score 6 · Accepted Answer · answered May 03 '18 at 18:08

Often VBA starts working on objects when these objects are still not ready to be worked on. Even copying an object may not be finished (i.e., the whole object is not totally committed to the clipboard) when VBA tries to paste.

I've found that putting certain operations into a separate procedure may be enough to make VBA to wait for one background process to finish before kicking off the next.

For example, in the code below, I've moved the Paste out of the main procedure. This makes VBA wait until the copy is done before pasting, and also until the paste is done before positioning the pasted chart.

In fact, I often have three separate functions which are called by the main sub: copying the chart, pasting the chart, and positioning the chart.

Public Sub CopyPasteHeadcountTopGraph()
    If PPT Is Nothing Then Exit Sub
    If PPT_pres Is Nothing Then Exit Sub

    Dim rng As Range
    Dim mySlide As Object
    Dim myShape As Object
    Dim cht As Chart

    Set mySlide = PPT_pres.Slides(6)

    With mySlide
    .Select
    Set cht = ThisWorkbook.Worksheets("Headcount").ChartObjects("HcChart").Chart

       cht.CopyPicture Appearance:=xlScreen, Format:=xlPicture, Size:=xlScreen

       '''''''''''''''''''''''''''''''''''
       '' .Shapes.Paste.Select 'ERROR HERE
       '''''''''''''''''''''''''''''''''''

       PasteChartIntoSlide mySlide

        PPT_pres.Windows(1).Selection.ShapeRange.Left = 35
        PPT_pres.Windows(1).Selection.ShapeRange.Top = 110
        PPT_pres.Windows(1).Selection.ShapeRange.Width = 655
        PPT_pres.Windows(1).Selection.ShapeRange.Height = 300

        End With

    'Clear The Clipboard
    Application.CutCopyMode = False
    Application.Wait (Now + TimeValue("00:00:01"))

End Sub

Function PasteChartIntoSlide(theSlide As Object) As Object
    theSlide.Shapes.Paste.Select
End Function

Thanks Jon, great explanation and this really improved my code, I was forced to use Sleep 50 within each function, but initially i had it waiting a full second so has drastically increased the code speed! — TisButaScratch, May 15 '18 at 15:13
Thank you for this. I wasted a few hours because I was not aware of this behaviour of VBA. It is not even mentioned in the documentation... — Rapwnzel, Dec 12 '18 at 09:39
@jonPeltier. I also had the same problems: many copies of charts to be pasted as images in another spreadsheet. I also do not see the error problem documented, but also note that the 'subroutine' suggestion, to cause the necessary processing to finish, has no explanation documented. My question1: Why does the subroutine force the needed processing? (Is that effect documented anywhere?) Question 2: If that does the trick, why do TisButaScratch and JonPeltier include the .waits, anyway ? — John, Jan 09 '19 at 05:21
The problem is not documented by Microsoft; it's sporadic, and you'll see questions in online forums about it. The 'subroutine' explanation is this: I have discovered that identifying out operations that take time and may get VBA out of sync, and breaking them into other procedures, seems to help with these problems. I don't know why it works, but I surmise it may be because the boundaries of the called procedures force VBA to finish working with objects before entering or leaving these procedures. Makes sense to me, anyway. — Jon Peltier, Jan 18 '19 at 15:54
I try to avoid using `Application.Wait` (although I see that I left it in the code sample in my answer, which came from the code sample in the question). `DoEvents` and calling other procedures keep VBA running, whereas `.Wait` actually halts VBA. It seems to me that halting VBA would prevent it from catching up with itself. — Jon Peltier, Jan 18 '19 at 15:56

score 0 · Answer 2 · edited Feb 05 '19 at 00:24

I had to use this code to solve this problem. The call to the function or DoEvents were not enough:

...
ActiveChart.ChartArea.Copy
aplicacion = RUTA & "\freemem.vbe"
lngErr = ShellExecute(0, "OPEN", aplicacion, "", "", 0)
Call pegadatos(PPSlide)
...

where the shellexecute function is declared previuously as

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

freemem.vbe is a text file with an only line: Mystring=(80000000). You must change the extension to .vbe after creating the .txt file.

And pegadatos:

Sub pegadatos(TheSlide As Object)
TheSlide.Shapes.Paste.Select
End Sub

score 0 · Answer 3 · answered Jul 12 '19 at 12:54

This error may also be due to an Excel bug that apparently prevents you from copying charts whose data labels are linked to a non-contiguous range of cells, such that you get the "Clipboard is empty..." error when you subsequently try to paste. This bug was reproduced in Excel 2013 and newer, and is additionally described at:

https://answers.microsoft.com/en-us/msoffice/forum/all/chart-with-data-labels-will-not-copy-to-clipboard/e60dcb59-8644-464a-8e13-0f5f62c4be76?auth=1

Error when copy pasting a chart through VBA

3 Answers3

Linked