Thread: Cannot copy&paste chart to new sheet (runtime 1004 error)

Hi Members,

OK, this is ridiculous....I feel very incompetent right about now.

Using the macro recorder, I am attempting to copy a chart from one sheet and paste it into "sheet2" (as a chart, not a picture etc). This is the code it generates (excel 2007).

Sub copyandpastechart()

    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.ChartArea.Copy
    Sheets("Sheet2").Select
    Range("D7").Select
    ActiveSheet.Paste
End Sub

I continually get a 'Run-time error 1004: paste method of worksheet class failed' message.


From searching around, it seems that a lot of people have similar problems, for which people have suggested a multitude of possible solutions.

However, no matter how I modify the code in accordance with those suggestions, nothing works!!!

What's up with this crazy behavior??

Thanks for any advice.

Your code worked for me.

Does this alternative work for you?

Sub copyandpastechart()

    ActiveSheet.ChartObjects("Chart 1").Duplicate
    With Sheets("Sheet2")
        ActiveSheet.ChartObjects(ActiveSheet.ChartObjects.Count).Chart.Location Where:=xlLocationAsObject, Name:=.Name
        .ChartObjects(.ChartObjects.Count).Left = .Range("D7").Left
        .ChartObjects(.ChartObjects.Count).Top = .Range("D7").Top
    End With

End Sub

Or this

Sub copyandpastechart()

    With ActiveSheet.ChartObjects("Chart 1").Duplicate
        .Chart.Location Where:=xlLocationAsObject, Name:="Sheet2"
    End With
    With ActiveChart.Parent
        .Left = Range("D7").Left
        .Top = Range("D7").Top
    End With

End Sub

Originally Posted by Andy Pope

Your code worked for me.

Does this alternative work for you?

Sub copyandpastechart()

    ActiveSheet.ChartObjects("Chart 1").Duplicate
    With Sheets("Sheet2")
        ActiveSheet.ChartObjects(ActiveSheet.ChartObjects.Count).Chart.Location Where:=xlLocationAsObject, Name:=.Name
        .ChartObjects(.ChartObjects.Count).Left = .Range("D7").Left
        .ChartObjects(.ChartObjects.Count).Top = .Range("D7").Top
    End With

End Sub

Or this

Sub copyandpastechart()

    With ActiveSheet.ChartObjects("Chart 1").Duplicate
        .Chart.Location Where:=xlLocationAsObject, Name:="Sheet2"
    End With
    With ActiveChart.Parent
        .Left = Range("D7").Left
        .Top = Range("D7").Top
    End With

End Sub

Hi Andy,

Both of those codes worked well for me. A big THANKS.

Oddly, the code I posted worked OK in Excel 2002 but not 2007.

For the benefit of others, here is the final code:

(copy chart 1 in sheet 1, check to see if a previous copy exists in sheet 2, delete it if it does, then paste the newly copied chart into sheet 2, resize it and deselect it)

Sub copyandpastechart()

Dim chrt As Object
Dim shtname As String

shtname = "sheet2"

With Worksheets(shtname)
If .ChartObjects.Count <> 0 Then
For Each chrt In .ChartObjects
chrt.Delete

Next chrt

End If

End With

With ActiveSheet.ChartObjects("Chart 1").Duplicate
.Chart.Location Where:=xlLocationAsObject, Name:="Sheet2"

End With

With ActiveChart.Parent
        .Left = Range("D7").Left
        .Top = Range("D7").Top
        .Width = 400
        .Height = 400

End With

ActiveWindow.Visible = False
ActiveCell.Select

End Sub

I have one question though:

I notice that every time I reiteratively copy&paste a chart ("Chart 1") from sheet 1 to sheet 2, the chart in sheet 2 becomes sequentially numbered rather than being consistently numbered as simply "Chart 2".

Is there any way of being able to fix the numbering such that it does not increase sequentially with each successive iteration of the macro execution?

I want to do this because I'd like to be able to consistently refer to the newly pasted Chart elsewhere.