Copy charts from all sheets to one

[AdamWojcik]

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]

@jdubon 

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?

[#3]

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.

[#4]

@jdubon 

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