CopyPicture method of range class failed using RangeName

[CG]

All,

I have a worksheet where I am cycling through some pivot tables and
copying a range defined by a range name to an output sheet. The range
being copied includes a chart and a few rows of cells. I keep getting
a
Run-Time Error '1004':
CopyPicture method of range class failed

I believe the syntax is correct because if I use error code and use
resume it will continue after 0 to <15 tries?

Anyone have any experience with this issue?

I have tried both options below:
Range(strRangeName).CopyPicture Appearance:=xlScreen,
Format:=xlPicture

Worksheets(SourceSheetName).Range(strRangeName).CopyPicture
Appearance:=xlScreen, Format:=xlPicture

 

[minimaster]

For debugging in the VBE first i would add a watch for strRangeName.
Sounds like that name sometimes isn't correct.

 

[Peter T]

I assume you are using Excel 2007. CopyPicture with the Format:=xlPicture
argument fails with Range and Chart objects. I filed this as a bug quite a
while ago together with a workaround, namely use Format:=xlBitmap

It seems there's no chance of it being fixed.

Regards,
Peter T

 

[CG]

I assume you are using Excel 2007. CopyPicture with the Format:=xlPicture
argument fails with Range and Chart objects. I filed this as a bug quite a
while ago together with a workaround, namely use Format:=xlBitmap

It seems there's no chance of it being fixed.

Regards,
Peter T

Peter,

I am using XL 2003. I believe the problem was I had a subroutine that
used some small arrays of objects and I was not setting them to
Nothing at the end of the routine. They were called several times and
the more the code ran the bigger the problem got. Does that make
sense to anyone?

CG

 

[Peter T]

Both versions of the code you originally posted should work fine in Excel
2003 or earlier, assuming you are returning a valid range object in the
first part before you get to .CopyPicture

If your object variable arrays are declared at procedure level there is no
need to explicitly destroy them, they will loose scope get cleaned up very
efficiently with VBA's garbage collection.

It's a good idea to destroy module level objects when not needed with Set
myObjVar = Nothing, however using them repeatedly should not cause any
problems.

Regards,
Peter T

Peter,

 

[Don Ell]

Here is a workaround that I found by 'accident'.

There is a timing error with the CopyPicture function during run-time execution in Excel 2007.

To see if you have this error, after the program faults, go to the debugger screen (VBA Editor screen) and press 'F8', wait a couple seconds, 'F8' again, wait a couple seconds, etc.

If after several tries (with a wait between each) the program advances properly, then you need to add some Application.Wait commands prior to your CopyPicture command.

In my case, the solution required TWO separate Wait commands. The first was after a file close command about 5 lines up (a 5 second wait), then the second wait was just after the Set= command which sets the Range to be copied (a one second wait). The point being that I had to test several different Wait commands in different locations until I found the combination which worked.

Sure wish MS was a little more careful with their pointer/subsequent command management....