Copying and pasting chart

[tiredofit]

This is the code I use to copy a chart from one worksheet to another:

        Set ch = Sheet1.ChartObjects("Chart")
       
        ch.Copy
               
        Sheet2.Paste Destination:=Sheet2.Cells(1, 1)

The program crashes with the error message:

run-time error '1004':

Method 'Paste' of object'_worksheet' failed

I click Debug and it points to this line:

        Sheet2.Paste Destination:=Sheet2.Cells(1, 1)

but when I click F5 to continue, it runs until then end of the code. Normally if there's an error, it shouldn't let you continue.

Can someone explain what is wrong?

Thanks

 

[NdNoviceHlp]

Hi tiredofit. I think you need to either select or activate sheet 2 before you paste the chart to it. HTH, Dave

 

[tiredofit]

Hi tiredofit. I think you need to either select or activate sheet 2 before you paste the chart to it. HTH, Dave

Tried that but it didn't work.

 

[NdNoviceHlp]

Trial pasting to a range instead. Dave

Sheet2.Paste Destination:=Sheet2.Range("A1")

 

[tiredofit]

Trial pasting to a range instead. Dave

Sheet2.Paste Destination:=Sheet2.Range("A1")

Still failed I'm afraid.

The behaviour is weird because once it crashes, I shouldn't be able to continue but I can.

 

[NdNoviceHlp]

Google...

Copy Charts from one sheet to another

I have two Sheets, and would like to copy the Chart from sheet1 to sheet2. I am using the below code, the code is removing the Chart from Sheet1 and pasting them into sheet2. Instead, I would just

stackoverflow.com

Not sure what's different other than setting the range first? Dave

 

[Jon Peltier]

This code requires neither sheet to be active.

Sheet1.ChartObjects("Chart").Copy
Sheet2.Paste
With Sheet2.ChartObjects(Sheet2.ChartObjects.Count)
    .Top = Sheet2.Range("A1").Top
    .Left = Sheet2.Range("A1").Left
End With

Note that Sheet1 and Sheet2 are not necessarily the sheet names showing on the worksheet tabs (though they might be), but they are the values listed for each sheet in the (Name) property in the VB Editor's Properties Pane.

 

[tiredofit]

This code requires neither sheet to be active.

Sheet1.ChartObjects("Chart").Copy
Sheet2.Paste
With Sheet2.ChartObjects(Sheet2.ChartObjects.Count)
    .Top = Sheet2.Range("A1").Top
    .Left = Sheet2.Range("A1").Left
End With

Note that Sheet1 and Sheet2 are not necessarily the sheet names showing on the worksheet tabs (though they might be), but they are the values listed for each sheet in the (Name) property in the VB Editor's Properties Pane.

Thanks for pointing this out.

My patience was diminishing and before I saw your response, I rewrote my code to add charts instead of copying from an existing one!

Nevertheless, this problem has enabled me to identify yet another Excel quirk, ie the fact you can press continue after a error is strange.