none
Copy a Chart RRS feed

  • 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

    Monday, May 15, 2017 4:16 PM

Answers

  • 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 "グラフ1", 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)

    • Edited by Ashidacchi Tuesday, May 16, 2017 8:48 AM
    • Marked as answer by py1 Tuesday, May 16, 2017 8:49 AM
    Tuesday, May 16, 2017 8:47 AM

All replies

  • 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
    • Edited by Ashidacchi Tuesday, May 16, 2017 1:02 AM
    Tuesday, May 16, 2017 1:01 AM
  • 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

    Tuesday, May 16, 2017 7:49 AM
  • 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
    • Edited by Ashidacchi Tuesday, May 16, 2017 8:02 AM
    Tuesday, May 16, 2017 8:01 AM
  • 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

       

    Tuesday, May 16, 2017 8:08 AM
  • 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 "グラフ1", 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)

    • Edited by Ashidacchi Tuesday, May 16, 2017 8:48 AM
    • Marked as answer by py1 Tuesday, May 16, 2017 8:49 AM
    Tuesday, May 16, 2017 8:47 AM
  • ok thanks for your help
    Tuesday, May 16, 2017 8:49 AM