Forum Discussion

AdamWojcik's avatar
AdamWojcik
Copper Contributor
Jul 10, 2021

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...
Excel
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Jul 10, 2021

    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

HansVogelaar's avatar
HansVogelaar
MVP

df17c 

The error must be caused by a different line.

Could you post the complete macro?

df17c's avatar
df17c
Copper Contributor
Aug 09, 2022

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. HansVogelaar 

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Aug 09, 2022

    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?
    • df17c's avatar
      df17c
      Copper Contributor
      Aug 09, 2022
      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")
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Aug 09, 2022

        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

         

Resources