Macro to create charts?

[Ed]

I recorded a macro to create charts using the Wizard, but ran into a
problem. My X-axis is in Col. A. The label is in A1, and the data starts
in A5 (time). I modified the macro to use the last data row, and I also
modified it to use other ranges for data, keeping Col A as my X-axis. This
version, though, gave me fits on one particular sheet.

Cols B - J are data to be plotted by the lines in the chart. On one
worksheet, however, the sensor for the Col B data didn't record, and the
recorder program filled the column with "NONE". The Wizard and the macro
*insisted* on grouping A and B together for my X-axis on this sheet, though
it worked okay for the other sheets. Looking at the macro, I realized there
isn't anything (that I can see) that specifies which column is the X-axis

Is there a better way to write this?

Ed

Sub Macro9()
'
Dim LastRow As Long
LastRow = ActiveWorkbook.Sheets("Sheet1").Range("A65536").End(xlUp).Row


Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range( _
"A1,B1:J1,A5:A" & LastRow & ",B5:J" & LastRow), PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
End Sub

 

[Jon Peltier]

Ed -

Omit the SetSourceData command. Instead, add each series and set its source
data separately:

Sub DoChart()
Dim LastRow As Long
Dim iSrs As Long
LastRow = 10

With Charts.Add
' Remove Extraneous Series
Do While .SeriesCollection.Count > 0
.SeriesCollection(1).Delete
Loop
.ChartType = xlLine
' Add Series
For iSrs = 1 To 9
With .SeriesCollection.NewSeries
.Values = Sheets("Sheet1").Range("A5:A" & LastRow).Offset(, iSrs)
.XValues = Sheets("Sheet1").Range("A5:A" & LastRow)
.Name = Sheets("Sheet1").Range("A1").Offset(, iSrs)
End With
Next
End With

End Sub

More on VBA charting:

http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

- Jon

 

[Ed]

Thank you, Jon. I appreciate the help.
Ed

Ed -

Omit the SetSourceData command. Instead, add each series and set its
source data separately:

Sub DoChart()
Dim LastRow As Long
Dim iSrs As Long
LastRow = 10

With Charts.Add
' Remove Extraneous Series
Do While .SeriesCollection.Count > 0
.SeriesCollection(1).Delete
Loop
.ChartType = xlLine
' Add Series
For iSrs = 1 To 9
With .SeriesCollection.NewSeries
.Values = Sheets("Sheet1").Range("A5:A" & LastRow).Offset(, iSrs)
.XValues = Sheets("Sheet1").Range("A5:A" & LastRow)
.Name = Sheets("Sheet1").Range("A1").Offset(, iSrs)
End With
Next
End With

End Sub

More on VBA charting:

http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______