CopyPicture method of range class failed - sometimes

Question

I have a VBA code which I am using to copy ranges as a picture and paste them into a chart. It does this so I can save it into a picture. This code has like a 70% success rate, and when it doesn't work, it gives out the error "CopyPicture method of range class failed". I don't understand why it can sometimes work and sometimes doesn't given that it is taking the same inputs.

Can anyone help?

Public Sub ExportRange(workbookPath As String, sheetName As String, rangeString As String, savepath As String)

    Set tempWorkBook = Workbooks.Open(workbookPath)

    Dim selectRange As range
    Set selectRange = Worksheets(sheetName).range(rangeString)
    Dim numRows As Long
    numRows = selectRange.Rows.Count
    Dim numCols As Long
    numCols = selectRange.Columns.Count

    ' Transfer selection to a new sheet and autofit the columns
    selectRange.Copy
    Dim tempSheet As Worksheet
    Set tempSheet = Sheets.Add
    tempSheet.range("A1").PasteSpecial xlPasteAll

    ActiveSheet.UsedRange.Columns.AutoFit
    Set selectRange = ActiveSheet.UsedRange
    selectRange.Select
    selectRange.CopyPicture xlScreen, xlPicture

    Dim tempSheet2 As Worksheet
    Set tempSheet2 = Sheets.Add
    Dim oChtobj As Excel.ChartObject
    Set oChtobj = tempSheet2.ChartObjects.Add( _
        selectRange.Left, selectRange.Top, selectRange.Width, selectRange.Height)

    Dim oCht As Excel.Chart
    Set oCht = oChtobj.Chart
    oCht.Paste
    oCht.Export filename:=savepath
    oChtobj.Delete

    Application.DisplayAlerts = False
    tempSheet.Delete
    tempSheet2.Delete
    tempWorkBook.Close
    Application.DisplayAlerts = True

End Sub

Answer 1

I was struggling with the very same issue than you and I think is nothing to do with our VBA code or lack of programming skills. The error it's too random.

Moreover, if after getting the error message I clicked DEBUG and pressed F8 to continue executing the code step by step, then I was able to skip the error. After the problematic line I pressed F5 to continue in normal execute mode.

Of course, the above is not a solution but reveals nothing wrong with my coding.

Well, I did this and it worked for me:

before this sentence,

rgToPic.CopyPicture Appearance:=xlScreen, Format:=xlBitmap

I added this one:

rgToPic.Copy  'just for nothing

and I never have had the error in CopyPicture method again.

---

Looking for this issue in other places I found out some users were able to skip the error by introducing this sentence before the CopyPicture method:

    application.CutCopyMode=false

Answer 2

The only thing that worked for me was to add a delay BEFORE the CopyPicture method. We are tweaking it shorter as I type this, but I know a 50 ms delay was working fine:

Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

'Set Range you want to capture

Dim rgExp As Range: Set rgExp = Range("B2:D6")

Sleep (50) ' Pause in milliseconds to prevent runtime error on CopyPicture, your system may be able to use shorter sleep, or may need longer...

' Copy range as picture onto Clipboard

rgExp.CopyPicture Appearance:=xlScreen, Format:=xlBitmap