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

 

  • 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

  • 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

    • AdamWojcik's avatar
      AdamWojcik
      Copper Contributor
      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.
    • nbisceglie's avatar
      nbisceglie
      Copper Contributor
      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?
    • jdubon's avatar
      jdubon
      Copper Contributor

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

      • 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
  • 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?
    • 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
        It's giving me a runtime error '9': Subscript out of range

Share

Resources