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

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?

HansVogelaar's avatar
HansVogelaar
MVP
Oct 26, 2022

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
  • jdubon's avatar
    jdubon
    Copper Contributor
    Oct 26, 2022
    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
    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Oct 27, 2022

      jdubon 

      That code expects that the workbook has 5 sheets, and that you want to place pictures on the 2nd to 5th sheet, at the specified cells.

      The code will cause an error if the workbook has more than 5 sheets.

       

      Could you explain what you want to do?

      • jdubon's avatar
        jdubon
        Copper Contributor
        Oct 27, 2022
        There are a few things I am hoping to accomplish but the first is I want to place pictures on sheets 2 - 5 into specific cells on sheet 1. Some sheets have multiple pictures - when I run the code it is pulling all pictures into sheet 1 but the ones with the multiple pictures are placed overlapping in one cell.

Resources