Copy charts from all sheets to one

Copper 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
            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


22 Replies


That code expects that the workbook has 5 sheets, and that you want to place pictures on the 2nd to 5th sheet, at the specified cells.

The code will cause an error if the workbook has more than 5 sheets.


Could you explain what you want to do?

There are a few things I am hoping to accomplish but the first is I want to place pictures on sheets 2 - 5 into specific cells on sheet 1. Some sheets have multiple pictures - when I run the code it is pulling all pictures into sheet 1 but the ones with the multiple pictures are placed overlapping in one cell.


If you want help with this, please provide detailed and specific information.