Jul 10 2021 02:58 AM
Jul 10 2021 02:58 AM
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
Jul 10 2021 03:13 AMSolution
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
Jul 10 2021 03:55 AM
Apr 20 2022 01:39 PM
Apr 20 2022 02:47 PM - edited Apr 21 2022 09:13 AM
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.
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
Apr 20 2022 03:18 PM
Replace the lines
For i = 2 To sheetCount
For Each Cht In ActiveWorkbook.Sheets(i).ChartObjects
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
Apr 20 2022 03:25 PM