VBA Chart object copying and positioning charts

[AndrewBT]

Hi all

I tried to create a macro in Excel 2002 as below to copy/paste a selected chart and position the copy exactly on top of the original. I thought it would be simple, but it wasn't for me, and the copy is offset.

I tried outputting the Left/Top/Width/Height properties (also for ChartArea) into cells for the two charts and they were the same so it looks like I haven't understood things properly.

I thought that after pasting the pasted object would be selected, and if that's not the case that could be my problem as I'm "moving" the original instead of the pasted version. If that's it, then a pointer to how I can select the pasted chart would be appreciated!

Thanks

Andrew

Sub DoIt()
Dim myChart, newChart As Chart

If TypeName(Selection) <> "ChartArea" Then
MsgBox ("Please select a chart first")
Exit Sub
End If

Set myChart = Selection.Parent
myChart.Parent.Copy
ActiveSheet.Paste

Set newChart = Selection.Parent

With newChart.Parent
.Left = myChart.Parent.Left
.Width = myChart.Parent.Width
.Top = myChart.Parent.Top
.Height = myChart.Parent.Height
End With

End Sub

 

[pgc01]

Hi Andrew

I don't have excel 2002, but this works in excel 2010.

Please test.

Select a chart in the worksheet and run:

Sub test()
Dim chtObj1 As ChartObject
Dim chtObj2 As ChartObject
 
Set chtObj1 = ActiveChart.Parent
Set chtObj2 = chtObj1.Duplicate.Chart.Parent
 
With chtObj2
    .Left = chtObj1.Left
    .Top = chtObj1.Top
    .Left = chtObj1.Left
    .Left = chtObj1.Left
End With
End Sub

 

[AndrewBT]

Hi there

Yes, that worked.

Many thanks!

Andrew

 

[pgc01]

I'm glad it worked! Thanks for the feedback.