Dynamic chart generation: Run time Error '1004: Method 'Cells' of object'_Global' fai





I am trying to generate a chart with multiple series. The number o
series is not fixed and can be any number. I recorded a macro and the
modified the code. 'Counter' refers to the number of the series and
have referenced it in a worksheet. I am getting the error in the lin
that is bold. Thank you for your help.

The code is as follows:

Dim Counter As Integer
Dim i As Integer

Sheets("With TC").Select
Counter = Cells(38, 13).Value
ActiveChart.ChartType = xlLine
With Sheets("VP_WithTC")
ActiveChart.SetSourceData Source:=.Range(.Cells(2, 13), .Cells(Counter
16)), PlotBy _
End With
For i = 2 To Counter
With ActiveChart.SeriesCollection.NewSeries

.XValues = ActiveSheet.Range("E1:H1")
End With

Next i

ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Value Path Graph"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Criteria"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Normalize
End With

End Su



Jon Peltier

If your active chart is a chart sheet, then the active sheet is the chart,
not a worksheet. So ActiveSheet.Cells will break.

You could add the chart directly as an embedded chart object, thus keeping
the workbook as the active sheet. Even if the chart is active, you can
reference the cells on the worksheet below.

The syntax is

ActiveSheet.ChartObjects.Add(left, top, width, height)

The dimensions in parentheses are for the embedded chart object, in points.

More VBA charting tips:


- Jon

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question