I am in needs of creating a chart for each worksheet in my workbook (roughly 10, but could be 12) - I recorded a macro, and added in the syntax I have used before to iterate workbooks and have come up with the below. Now my issue that I see right off the bat is that the syntax specifies Chart 1
and once that name has been used it can not be re-used.
How would this syntax be altered in order to make it re-usable for inserting the exact same chart on multiple worksheets in a workbook?
Sub CreateChart()
Dim WS_Count As Integer, I As Integer
WS_Count = ActiveWorkbook.Worksheets.Count
For I = 1 To WS_Count
Range("A1:I2").Select
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$I$2")
ActiveSheet.Shapes("Chart 1").ScaleWidth 1.9416666667, msoFalse, _
msoScaleFromBottomRight
ActiveSheet.Shapes("Chart 1").ScaleHeight 1.4531248177, msoFalse, _
msoScaleFromBottomRight
ActiveChart.ClearToMatchStyle
ActiveChart.ChartStyle = 205
Next I
End Sub
EDIT
The workbook has roughly 10 worksheets in it, each worksheet contains the data for the chart in A1:I2 - I need to graph that data on each individual worksheet.
Does that help clarify?
Try the code below
Just not sure where you want the Chart's source suppose to be from, are they all from Range("Sheet1!$A$1:$I$2")
, from "Sheet1" ? or from their sheet ?
Sub CreateChart()
Dim ws As Worksheet
Dim Chart As Shape
For Each ws In ThisWorkbook.Worksheets
Set Chart = ws.Shapes.AddChart2(201, xlColumnClustered)
With Chart
.Chart.SetSourceData ws.Range("$A$1:$I$2")
.ScaleWidth 1.9416666667, msoFalse, msoScaleFromBottomRight
.ScaleHeight 1.4531248177, msoFalse, msoScaleFromBottomRight
.Chart.ClearToMatchStyle
.Chart.ChartStyle = 205 ' why do you have this line ? why not define the chart style as 205 in the first line ?
End With
Next ws
End Sub