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 · Accepted 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

[#3]

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.

[#4]

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?

[#5]

@nbisceglie 

Which charts do you want to copy to which cell?

[#6]

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

[#7]

@nbisceglie 

Replace the lines

For i = 2 To sheetCount

For Each Cht In ActiveWorkbook.Sheets(i).ChartObjects
Cht.Copy
targetSheet.Paste targetSheet.Range("A1")
Next Cht

Next i

with

    Dim arr
    arr = Array("A1", "A20", "J1", "J20")
    For i = 1 To 4
        Set Cht = Sheets(targetSheet.Index + i).ChartObjects(1)
        Cht.Copy
        targetSheet.Paste targetSheet.Range(arr(i - 1))
    Next i

[#8]

You are a life saver! I've been working on this for 2 days! Thank you so much!!
I didn't think of creating an array. I'm going to add this one to my tool belt haha.

[#9]

Instead 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?

[#10]

@df17c 

Change the line

     Set Target_Sheet = ActiveWorkbook.Sheets(1)

to

     Set Target_Sheet = Workbooks.Add(xlWBATWorksheet).Sheets(1)

[#11]

It's giving me a runtime error '9': Subscript out of range

[#12]

@df17c 

The error must be caused by a different line.

Could you post the complete macro?

[#13]

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 = Workbooks.Add(xlWBATWorksheet).Sheets(1)

For i = 1 To Sheet_Count
For Each Cht In ActiveWorkbook.Sheets(i).ChartObjects
Cht.Copy
Target_Sheet.Paste Target_Sheet.Range("A1")
Next Cht
Next i
End Sub

It's telling me that I need to debug this line:

For Each Cht In ActiveWorkbook.Sheets(i).ChartObjects

Not sure how to copy paste the code like you've been doing. But my code is long, I've written code that allows me to pull multiple files of data and organize them into separate sheets. And then another code that allows me to run macros on all of the sheets that further organizes and creates graphs of the data. And then I just want to be able to take all of the graphs from each sheet and paste them into a new separate workbook. @Hans Vogelaar 

[#14]

@df17c 

• Does your workbook contain chart sheets, as opposed to worksheets with cells?
• Are the charts that you want to copy embedded on worksheets, or are they on separate chart sheets, or a mixture of both?

[#15]

1. My workbook contains worksheets with cells.

2. Here is the code I used to graph my charts using data on each sheet. The code creates charts from data that is embedded on each sheet in my workbook.

Range("A12").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Shapes.AddChart2(240, xlXYScatterSmoothNoMarkers).Select
ActiveChart.SetSourceData Source:=Range("$A$1:$QT$9")

[#16]

@df17c 

Try this version:

Sub CopyCharts()
    Dim Sheet_Count As Integer
    Dim Target_Sheet As Worksheet
    Dim i As Integer
    Dim Cht As ChartObject

    Sheet_Count = ThisWorkbook.Sheets.Count
    Set Target_Sheet = Workbooks.Add(xlWBATWorksheet).Sheets(1)

    For i = 1 To Sheet_Count
        For Each Cht In ThisWorkbook.Sheets(i).ChartObjects
            Cht.Copy
            Target_Sheet.Paste Target_Sheet.Range("A1")
        Next Cht
    Next i
End Sub

[#17]

Awesome! Thank you, great job, it worked!

[#18]

@Hans VogelaarIn step 6) is there a way to have the charts paste into their own cell verse pasting on top of each other?

[#19]

@jdubon 

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

[#20]

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