SOLVED

Copy charts from all sheets to one

New Contributor

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

 

2 Replies
best response confirmed by AdamWojcik (New Contributor)
Solution

@AdamWojcik 

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

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.