Automating Chart

[RisingStar]

I have to regularly create charts in excel and want to automate this using a
macro/vba. The data to be plotted is: X-axis date & Y-axis: a number value
different for each date. The trouble is the number of these values (rows) is
different for each set. I therefore cannot keep my Data Series rows the same
as this varies. Can anybody tell me how to automate a graph by specifying
the series as all cells having values and omitting empty cells. e.g. My
spreadsheet has dates from A1:A23 and values in B1:B23. The next sheet has
dates in A1:A10 & values B:1:B10........so on. Cells below A23,B23 & A10,B10
are always empty.
Please help!

risingstar

 

[Jon Peltier]

RS -

You could put this macro into your personal.xls personal macro workbook
file, activate a workbook that has times in column A and values in
column B in all of its sheets, then run the macro.

Sub MakeCharts()
Dim wks As Worksheet
Dim chtob As ChartObject
Dim newsrs As Series
For Each wks In ActiveWorkbook.Worksheets
Set chtob = wks.ChartObjects.Add _
(ActiveWindow.Width * 0.24, ActiveWindow.Height * 0.23, _
ActiveWindow.Width * 0.48, ActiveWindow.Height * 0.46)
With chtob.Chart
.ChartType = xlLineMarkers
Set newsrs = .SeriesCollection.NewSeries
End With
With newsrs
.Values = wks.Range(wks.Cells(1, 2), _
wks.Cells(1, 2).End(xlDown))
.XValues = wks.Range(wks.Cells(1, 1), _
wks.Cells(1, 1).End(xlDown))
End With
Next
End Sub

- Jon