Search code examples
vbaexcelexcel-2013

Create A Chart On Each Worksheet Using The Data From That Worksheet

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?

Solution

  • 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