Forum Discussion
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...
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 Sub6) All charts will be pasted more or less on top of each other
- 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?
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")
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")
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- Awesome! Thank you, great job, it worked!