Forum Discussion
Copy charts from all sheets to one
1) You have two For loops, but only one Next.
2) You declare a variable Target_Sheet.
3) You don't use the keyword Set to assign Target_Sheet
3) You try to paste to the undefined variable ChartSheet
4) You try to paste to a string instead of a range.
5) Chart is not a good name for a variable, since it is the name of a built-in object.
Here is a working version:
Sub CopyCharts() Dim Sheet_Count As Integer Dim Target_Sheet As Worksheet Dim i As Integer Dim Cht As ChartObject Sheet_Count = ActiveWorkbook.Sheets.Count Set Target_Sheet = ActiveWorkbook.Sheets(1) For i = 2 To Sheet_Count For Each Cht In ActiveWorkbook.Sheets(i).ChartObjects Cht.Copy Target_Sheet.Paste Target_Sheet.Range("D4") Next Cht Next i End Sub6) All charts will be pasted more or less on top of each other
1) You have two For loops, but only one Next.
2) You declare a variable Target_Sheet.
3) You don't use the keyword Set to assign Target_Sheet
3) You try to paste to the undefined variable ChartSheet
4) You try to paste to a string instead of a range.
5) Chart is not a good name for a variable, since it is the name of a built-in object.
Here is a working version:
Sub CopyCharts()
Dim Sheet_Count As Integer
Dim Target_Sheet As Worksheet
Dim i As Integer
Dim Cht As ChartObject
Sheet_Count = ActiveWorkbook.Sheets.Count
Set Target_Sheet = ActiveWorkbook.Sheets(1)
For i = 2 To Sheet_Count
For Each Cht In ActiveWorkbook.Sheets(i).ChartObjects
Cht.Copy
Target_Sheet.Paste Target_Sheet.Range("D4")
Next Cht
Next i
End Sub6) All charts will be pasted more or less on top of each other
- Thank you very very much! The ChartSheet variable is the result of edits to make it more readable for the question. Thanks for pointing out the "Chart" naming is not good.
- Is there a way to paste the charts in different cells? For example, can I have a chart be pasted in A1, A20, J1, and J20?
Which charts do you want to copy to which cell?
4 different charts on 4 different sheets. In my sub, i have created a new sheet to store the charts.
All the cell references I've made below are on the new sheet to store the charts in.
I want:
Sheet 1's chart I want to be in A1
Sheet 2's chart I want to be in A20
Sheet 3's Chart I want to be in J1
Sheet 4's Chart I want to be in J20
HansVogelaarIn step 6) is there a way to have the charts paste into their own cell verse pasting on top of each other?
For example:
Sub CopyCharts() Dim Sheet_Count As Integer Dim Target_Sheet As Worksheet Dim i As Integer Dim Cht As ChartObject Sheet_Count = ActiveWorkbook.Sheets.Count Set Target_Sheet = ActiveWorkbook.Sheets(1) For i = 2 To Sheet_Count For Each Cht In ActiveWorkbook.Sheets(i).ChartObjects Cht.Copy Target_Sheet.Paste Target_Sheet.Range("D" & i) Next Cht Next i End Sub- My apologies in advance as I am a newbie to VBA. When I made the change it returned an error. This is my original code with placement of my graphs:
Sub CopyCharts()
Dim Sheet_Count As Integer
Dim Target_Sheet As Worksheet
Dim i As Integer
Dim Cht As ChartObject
Sheet_Count = ActiveWorkbook.Sheets.Count
Set Target_Sheet = ActiveWorkbook.Sheets(1)
Dim arr
arr = Array("A4", "B4", "Q25", "B46")
For i = 2 To Sheet_Count
For Each Cht In ActiveWorkbook.Sheets(i).ChartObjects
Cht.Copy
Target_Sheet.Paste Target_Sheet.Range(arr(i - 1))
Next Cht
Next i
End Sub
