Forum Discussion
Copy charts from all sheets to one
I have written a macro that looks like this:
Sub CopyCharts()
Dim Sheet_Count As Integer
Dim Target_Sheet As Worksheet
Dim i As Integer
Sheet_Count = ActiveWorkbook.Sheets.Count
Target_Sheet = ActiveWorkbook.Sheets(1)
For i = 2 To Sheet_Count
For Each Chart In ActiveWorkbook.Sheets(i).ChartObjects
Chart.Copy
ChartSheet.Paste ("D4")
Next i
End Sub
Its purpose is to copy all the charts from sheet 2 onwards to D4 on sheet 1.
It does so through these steps:
1. Get the sheet count of the active workbook
2. Set the copy target sheet to the first sheet
3. Loop through each but the first sheet
4. Inside that loop, copy all the chart objects to space D4 on the first sheet
When I try to run it, I get an error saying
Compile error:
Invalid Next control variable reference
The only place I use the Next instruction in is at the end of the For loop, which is how you're supposed to use it?
I'm new to both VBA and Office macros, so please forgive me if I'm making an obvious mistake
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 Sub
6) 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 Sub
6) All charts will be pasted more or less on top of each other
- AdamWojcikCopper ContributorThank 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.
- nbisceglieCopper ContributorIs 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?
- jdubonCopper Contributor
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
- df17cCopper ContributorInstead of copying all of the charts onto one sheet in the same workbook, is there a way to copy all of the charts into a new workbook?