Copy a Chart

Question

Hi

I have inherited an application which has a (complex) chart gebnerated on a particular sheet - the chart is not generated by VBA code but by refering to the data cells.

I am trying to find a  way to 'select' the chart and all its components and do a copy/paste onto antoher sheet.

is this possible?  I can't see anything obvious.

many thanks

Peter

Answer 1

Hi py1,

I've made a sample:
(1) create a button [Chart: Copy Paste] on the sheet [SourceSheet].
     A chart is in [SourceSheet].
      
(2) here's code:
  Copy a Chart (or Charts) in SourceSheet and paste it (them) in TargetSheet.

Private Sub btn_Chart_CopyPaste_Click()
  Dim obj As Object
  Worksheets("SourceSheet").Select
  For Each obj In Shapes
    'MsgBox "Object Type=" & obj.Type & ", Name=" & obj.Name
    If (obj.Type = 3) Then  ' -- Type:3 = Chart
      obj.Select
      obj.Copy
      Sheets("TargetSheet").Select
      Sheets("TargetSheet").Range("B10").Select
      ActiveSheet.Paste
    End If
  Next
End Sub

[note]
(1) In this code, I presume you don't know the name of Chart object, so I made a "For Each " Loop in order to search chart(s). 
(2) "Dim obj As Object" can be written like "Dim shp as Shape"
_______________
Ashidacchi

Answer 2

Thanks Ashidacchi.

I have found out the the name is actually "Chart1".

How can I amend to your code to cater for this?

Many thanks

Peter

Answer 3

How can I amend to your code to cater for this?

(1) What does "How can I amend to your code to cater for this?" mean?
Would you explain it with other words?
(2) I can share my sample file via OneDrive or Dropbox. Do you want it?
___________
Ashidacchi

Answer 4

Hi

In you r solution you said that you made the "For loop" to search for charts as you assumed that i didn't know the name of the chart.

All I meant was that I discovered that he chart was called "Chart1" so I just wandered how the code could be written now i.e. without the For Loop.

Does that make sense?

many thanks

Peter

   

Answer 5

Hi Peter,

Thank you for your good explanation. I could understand it.

If you know the name of a chart is "Chart1", I thought "For loop" would be unnecessary.
So, I modified code without "For loop", but could not run code successfully.

  cf. the name of a chart is "グラフ１", not "Chart1" in Japanese Excel.
     
  You can show the above message box by uncommenting [Msgbox "Object Type=" & ....]

Conclusion at this time:
I suppose it would be safer code that has "For loop", since the name of a chart would vary.
___________________________
Ashidacchi (Hideki ASHIDA)

Answer 6

ok thanks for your help