Copy chart from one sheet to another

[Groubas]

Could anyone help me with this one?

I want to copy a chart from one sheet to another. Now I've got this far on my own but its not woking like i want it to

Private Sub cbtnPrint_Click()

With ThisWorkbook
.Sheets("TempData").ChartObjects("Diagram 20").Chart.ChartArea.Copy
.Sheets("Plot").Paste
End With

Unload Me

End Sub


The first problem is that when pasted the chart area flashes, and it doesnt stop untill I double click on it (anyone herd of this problem before?).

The second problem is that the chart doesnt paste where I want it to on the sheet. I tried to solve this by drawing the "first" chart manually and then just pasting the "new" chart in that chart area. Like this:

Private Sub cbtnPrint_Click()

With ThisWorkbook
.Sheets("TempData").ChartObjects("Diagram 20").Chart.ChartArea.Copy
.Sheets("Plot").ChartObjects("Diagram 6").Chart.ChartArea.Paste
End With

Unload Me

End Sub

But that didnt work at all all I got was this error message "Object doesn't support this property or method"


Anyone with helpful advices?

 

[SkipVought]

Hi,

Problem 1 - I cannot duplicate -- works just fine

Problem 2 - assign the top and left properties of the chartobject to what you want.

If you want to align with C5, for instance, assuming that this chartobject is the ONLY chartobject on sheet Plot...

.....
    with .Sheets("Plot").ChartObjects(1)
        .top = [C5].top
        .left = [C5].left
    end with
end with

Skip,

_[red][/red]

 

[Groubas]

Ok thanks Skip, that looks good, but I wonder wouldnt I get a problem when I repeat the program and the second chart will be no 2 and so on... I mean since the code alignes ChartObject 1?

 

[SkipVought]

A simple counter will do, or use this for the chartobject index

.....
    with .Sheets("Plot").ChartObjects(.Sheets("Plot").ChartObjects.count)
        .top = [C5].top
        .left = [C5].left
    end with
end with

will always reference the LAST one

Skip,

_[red][/red]