Copy chart as picture on another worksheet INTERMITTENTLY fails

[mike.magill]

I have a routine that, amongst other things, alters some cell values that in turn determine the content of a series of charts. Each time the charts are changed, the routine copies the chart and pastes it as a picture into another worksheet.

I am 99% certain the code is sound but, for some reason, the routine will regularly but not always fail at one or more of the copy/paste parts. If you press debug and retry, the code will no longer fail and will continue perfectly. This suggest to me that the spreadsheet is slow in updating and the routine is trying to execute a line before the system is ready. The code that fails is similar to the following:

Sheet10.ChartObjects(ChrtName).CopyPicture
Sht.Paste Destination:=Sht.Range(PasteRange)

I have tried using an error handler that waits 3 seconds and then tries again up to 10 times but that does not always work (i.e. it fails more than 10 times).

    ErrorHandler:
    
    ErrorCount = ErrorCount + 1
    If ErrorCount < 10 Then
        Application.Wait (Now + TimeValue("0:00:03"))
        Resume
    Else
        MsgBox "Error message"
    End If

Does anyone have any idea why the error is happening or how I can improve the error handler? Also, if I run it without the error handler and just debug and resume each error the routine takes about 5 minutes but with the error handler it takes c.15 minutes (if it completes at all)!

 

[RoryA]

I'd suggest a DoEvents or two rather than Wait. You may also need to actually select the chart - I've seen many reports with exporting charts as pictures to disk where it produces a 0KB file if the chart isn't visible.