This will get you started. Not sure from your description if you need a
different series for each row. I also don't know where the chart title comes
from.


For Each sht In ActiveWorkbook.Sheets
Lastrow = sht.Range("A5").End(xlDown).Row
Charts.Add
Set newchart = ActiveChart
With newchart
.Activate
.ChartType = xlLine
.SetSourceData _
Source:=sht.Range("A5:AQ5,A" & Lastrow & ":AQ" & Lastrow), _
PlotBy:=xlRows
.Location Whe=xlLocationAsObject, Name:=sht.Name
.HasTitle = True
.ChartTitle.Characters.Text = "DTF26"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
Next sht

"James8309" wrote:

Hi everyone

I am trying to create many many many charts and because there are so
many I am trying to create Macro to do it. Let me explain what my end
result must be and what I ve done till now.

1. I have a sheet named "$" containing all the data.
(Workbooks("Report.xls").Sheets("$")

- Dates(i.e. Jul-07 etc) are in Range("A5:AQ5") this is my X-Axis
range and this is common X-Axis for all the chart that needs to be
done

- Then I have Chart title or series lable in Columns(A:A), number of
charts or lines I have in sheets("$") changes everytime.

- Actual Y-Axis values are in $ and they are from Columns(B:AQ)

2. I just recorded one macro to give an example;

Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData
Source:=Sheets("$").Range("A5:AQ5,A7:AQ7"), PlotBy _
:=xlRows
ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet4"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "DTF26"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

3. End results that I need to achieve are;

- As long as Cell in Column A is not empty, Create 'Line Chart' with
X-Axis from A5:AQ5 and Y- Axis data from that particular Row (i.e. if
it is doing 8th row Chart for A8 then it will be "A5:AQ5,A8:AQ8")

- Create chart in "Sheet4"

I defined the LastRow in Column A and tried to create a simple macro
where create chart as long as A & Rowcount < "". I keep getting
errors and my limited knowledge in VBA really makes this difficult.

Can anyone help?

Thank you in advance


Regards,


James