Thread: How to copy ChartObjects from one Worksheet to another?

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?

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

Sub CopyChart()
    
    Dim objCht As ChartObject
    Dim objDupe As ChartObject
    Dim strFormula As String
    
    Set objCht = Worksheets("Sheet1").ChartObjects(1)
    Set objDupe = objCht.Duplicate
    
    objDupe.Chart.Location xlLocationAsObject, "Sheet2"
    Set objDupe = ActiveChart.Parent
    
    With objDupe.Chart.SeriesCollection(1)
        strFormula = .Formula
        Debug.Print "Before", strFormula
        strFormula = Replace(strFormula, "Sheet1", "Sheet2")
        Debug.Print "After", strFormula
        .Formula = strFormula
    End With
    
End Sub

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

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.

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

Sub CopyCharts(rngFrom As Range, wksTo As Worksheet)

Dim _
  wksFrom As Worksheet _
, collChartObjectCollection As ChartObjects _
, coSrc As ChartObject _
, coTgt As ChartObject _
, coCopy As Object _
, cht As Chart _
, rngTopLeft As Range _
, dbTop As Double _
, dbLeft As Double _
, ser As Series _
, sName As String _
, sFormula As String _
, i As Integer _
, nCharts As Integer _
', rngFrom As Range _
', wksTo As Worksheet _

    Set wksFrom = rngFrom.Worksheet
    Debug.Print "Source Worksheet = " + wksFrom.Name
    For Each coSrc In wksFrom.ChartObjects
        Set cht = coSrc.Chart
        Debug.Print "  ChartObject = " + coSrc.Name + ", Parent = " + coSrc.Parent.Name
        Debug.Print "    Chart = " + cht.Name
        Set rngTopLeft = coSrc.TopLeftCell
        dbTop = coSrc.Top
        dbLeft = coSrc.Left
        coSrc.Copy
        wksTo.Paste Destination:=wksTo.Range(rngTopLeft.Address)
        Debug.Print "Target Worksheet = " + wksTo.Name
        nCharts = wksTo.ChartObjects.Count
        Set coTgt = wksTo.ChartObjects(nCharts)
        With coTgt
            .Chart.Location xlLocationAsObject, wksTo.Name
            Debug.Print "  ChartObject = " + .Name + ", Parent = " + .Parent.Name
'            Application.ScreenUpdating = False
            ' adjust formulae to look at the new page
            .Top = dbTop
            .Left = dbLeft
            .Name = Replace(.Name, UCase(wksFrom.Name), UCase(wksTo.Name))
            Set cht = coTgt.Chart
            Debug.Print "    Chart = " + cht.Name
            sName = cht.Name
            sName = Replace(sName, UCase(wksFrom.Name), UCase(wksTo.Name))
'            cht.Name = sName
            For Each ser In cht.SeriesCollection
                Debug.Print "      Series Name = " + ser.Name
                sFormula = ser.Formula
                Debug.Print "      Old Formula = " + sFormula
                sFormula = Replace(sFormula, UCase(wksFrom.Name), UCase(wksTo.Name))
                Debug.Print "      New Formula = " + sFormula
                ser.Formula = sFormula
            Next
            ' and the chart title too, if the formula is available; if not, the text is ok as is
            Debug.Print "      Old Title = " + cht.ChartTitle.Caption
            Debug.Print "             or = " + cht.ChartTitle.Text
            cht.ChartTitle.Caption = Replace(cht.ChartTitle.Caption, UCase(wksFrom.Name), UCase(wksTo.Name))
            Debug.Print "      New Title = " + cht.ChartTitle.Caption
            Debug.Print "             or = " + cht.ChartTitle.Text
'            Application.ScreenUpdating = True
        End With
    Next
    Debug.Print "Target Worksheet " + wksTo.Name
    For Each coSrc In wksTo.ChartObjects
        Set cht = coSrc.Chart
        Debug.Print "  ChartObject " + coSrc.Name
        Debug.Print "    Chart " + cht.Name
    Next
    wksTo.Activate
    wksTo.Select

End Sub

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.

can you post a workbook example that demonstrates the problem?

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.