How to copy ChartObjects from one Worksheet to another?

[ffffloyd]

What is the best way to copy embedded charts from one worksheet to another?

I see that a Worksheet contains a ChartObjects collection. I see that I can use the Duplicate method for each individual ChartObject, with a view to transferring the copy to the target Worksheet, but Duplicate returns an Object pointer, not a ChartObject pointer. How do I then manipulate the resultant ChartObject copy?

Where I think I am going to need to be careful in addition is that a number of properties (such as the data series, axis labels) point to a Range on the source Worksheet. I am assuming I will need to programmatically change them to point to the same regions on the target Worksheet. Or does the act of copying magically change them?

[Andy Pope]

You will need to update the series formula to reference the data on the new sheet.

Please Login or Register  to view this content.

[ffffloyd]

Thanks Andy. I think I've pretty well sorted it out now.

One question though. You've declared objDupe as ChartObject, which is the natural and intuitive thing to do. I found when I was doing it though that I had to declare it as Object. When I declared it as ChartObject I got an object mismatch (sorry, can't remember the exact message). When I looked at the Duplicate method, according to the Help Text, it returns type Object, not ChartObject.

In order to refer to the newly copied ChartObject (which I ended up moving via the Cut and Paste methods) I got to it by referring to Worksheets("Sheet2").ChartObjects(Worksheets("Sheet2").ChartObjects.Count).

Longwinded, but it did the job.

I was just wondering, did that code actually run for you (not that I'm wanting to have a go at you if it is untested, I appreciate the advice) and, if so, what version of Excel are you using? (I'm using Excel 2007 (12.0.6565.5003).)

[Andy Pope]

I always test my code before posting.
If I do happen to dash out a quick couple of lines from memory I will state that that is the case.

The code works in both xl2003 and xl2007.

It hard to say why you had to use a object as you did not post your code.
The help on the Duplicate method is a little unclear in xl2003 but it says it will return an object

expression Required. An expression that returns an object in the Applies To list.

This means it will return the same object type as that of the object using the Duplicate method, which in my code is a chartobject.

in xl2007 it's clearer as there is help specific to the chartobject duplicate method.

expression A variable that represents a ChartObject object.

[ffffloyd]

Thanks for your help so far, Andy. Here is my code as it presently exists:

Please Login or Register  to view this content.

I retrieve the target ChartObject as the last in the collection of ChartObjects because, believe me, it wouldn't accept coCopy as ChartObject. It has to be Object or the compiler yields "Type Mismatch". In any case, I have recently changed from using Duplicate to using Copy and Paste. The result is the same.

The above code works for most charts; however, my case is a little different. (Ain't that always the way?)

On each Worksheet, there can be a different number of x values and the row in the Worksheet where the list begins can even be different, so I need to make the chart dynamic. Now, I don't know what the usual method of doing that is, or even whether there is a usual method, but the way I do it is to have a standard place on the Worksheet that works out and lists the various ranges required. I then refer to those ranges using the INDIRECT( ) function.

Charts won't let you use INDIRECT( ) in the ranges you specify but they will let you use defined names, so I define the same set of local names for each worksheet, and they do use INDIRECT( ), and I can enter them in the charts' ranges. Being local names, each use needs to be preceded by the relevant worksheet's name (but that is also the case for directly specified ranges anyway; ie, you still need to tell it which Worksheet to get the specified range from).

So now, here is my problem: when I reach the line in the code that goes "ser.Formula = sFormula" I get Run-time error '1004': Application-defined or object-defined error, which suggests to me that the object I am assigning to does not exist. It certainly exists, so maybe it is corrupt in some way.

Curiously, if you look seven lines above that, there is a line that goes "cht.Name = sName" which is commented out. If I leave that in, I get an out-of-memory error. I am running on an 8 GB laptop and less than 2 GB are being used at the time. This also leads me to think there is something wrong with the object I have copied. Is there something else that needs to be set before I can alter the name or the series?

I reiterate that this technique does work with standard, directly specified ranges, so this appears to be a quirk of named values.

[Andy Pope]

can you post a workbook example that demonstrates the problem?

[ffffloyd]

I knew you were going to ask that! The thing is, the charts and their copying are dependent on so many other things that it is not easy to isolate.

I have found a workaround by replacing Values and XValues directly. It ain't pretty but at least the charts display what they're meant to.

Thanks for your help anyway.