Answered by:
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
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?
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 helpTuesday, May 16, 2017 8:49 AM