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

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

df17c 

Change the line

     Set Target_Sheet = ActiveWorkbook.Sheets(1)

to

     Set Target_Sheet = Workbooks.Add(xlWBATWorksheet).Sheets(1)
  • df17c's avatar
    df17c
    Copper Contributor
    Aug 08, 2022
    It's giving me a runtime error '9': Subscript out of range
    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Aug 08, 2022

      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 

Resources