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

nbisceglie's avatar
nbisceglie
Copper Contributor

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




HansVogelaar's avatar
HansVogelaar
MVP
Apr 20, 2022

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
  • nbisceglie's avatar
    nbisceglie
    Copper Contributor
    Apr 20, 2022
    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.