Macros to create a number of charts

[Dave]

Have 40 rows of data. I would like to create a macro
which creates separate charts for each row. I have been
practicing on three rows of data and have come up with a
macro like the one below. What it does is create three
charts but based on the same row of data. I know where
the problem is
** the line below that reads "ActiveChart.SetSourceData
Source:=Sheets("Sheet1").Range("A9:C9"), PlotBy:= _
xlRows"
but can't think how to fix it. Any ideas greatfully
appreciated.

-------------------------------------------------------
chart2 Macro
' Macro recorded 10/07/2003 by Vocational
'

'
Range("A9:C9").Select
Do Until ActiveCell = ""
charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets
("Sheet1").Range("A9:C9"), PlotBy:= _
xlRows
ActiveChart.Location Where:=xlLocationAsObject,
Name:="Sheet1"
ActiveChart.HasLegend = False
ActiveWindow.Visible = False
Windows("chart trial.xls").Activate
ActiveCell.Offset(1, 0).Range("A1").Select
Loop
End Sub

 

[J.E. McGimpsey]

one way:

Public Sub Chart2()
Dim wkSht As Worksheet
Dim cell As Range
Set wkSht = Sheets("Sheet1")
For Each cell In wkSht.Range("A9", Range("A9").End(xlDown))
With Charts.Add
.ChartType = xlColumnClustered
.SetSourceData _
Source:=cell.Resize(1, 3), _
PlotBy:=xlRows
.HasLegend = False
.Location _
Where:=xlLocationAsObject, _
Name:=wkSht.Name
End With
Next cell
End Sub