Copy chart as picture on another worksheet INTERMITTENTLY fails

mike.magill

New Member
Joined
Jun 24, 2009
Messages
22
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:

Code:
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).

Code:
    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)!
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,809
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

Forum statistics

Threads
1,148,158
Messages
5,745,108
Members
423,924
Latest member
Gazzat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top