Dynamic Chart troubles

[spflash]

Hi All,
I'm a VBA Newbie in need of some guidance. I have an attached spreadsheet with an embedded chart on the same page as the data. I also have the macro below. When this macro is executed on the spreadsheet it produces the chart shown, where the highlighted fields are the ones being plotted. It all seems to work fine except when the lines of data change from 5 to say 3 the macro will not excute. One line of data plots across with with column B the name range, columns G thru U the data range, and the heading row of columns G thru U the XValue range.
I've tried to use the offset equation and count function in defining my name ranges to help adjust for changes in the number of lines of data. However, I've been unsuccessful in trying to find a combination of name ranges and VBA code to make the macro work and properly maintain the same chart formats. Can anyone out there help me?
Thanks,

spflash (VBA Newbie)


[VBA]Sub CreatingChart()
Dim TB As Worksheet
Dim j%, lR%
Dim Rng$
Dim Ser As Series
Set TB = ActiveSheet
lR = Cells(65536, 1).End(xlUp).Row
For j = 4 To lR
Rng = TB.Range(TB.Cells(j, 1), TB.Cells(j, 21)).Address
Next j
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets(TB.Name).Range(Rng), PlotBy _
:=xlRows
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection (1)
ActiveChart.SeriesCollection(1).XValues = TB.Range("G3:U3")
ActiveChart.SeriesCollection(1).Values = TB.Range(TB.Cells(4, 7), TB.Cells(4, 21))
ActiveChart.SeriesCollection(1).Name = TB.Range("B4")
ActiveChart.SeriesCollection (2)
ActiveChart.SeriesCollection(2).XValues = TB.Range("G3:U3")
ActiveChart.SeriesCollection(2).Values = TB.Range(TB.Cells(5, 7), TB.Cells(5, 21))
ActiveChart.SeriesCollection(2).Name = TB.Range("B5")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection (3)
ActiveChart.SeriesCollection(3).XValues = TB.Range("G3:U3")
ActiveChart.SeriesCollection(3).Values = TB.Range(TB.Cells(6, 7), TB.Cells(6, 21))
ActiveChart.SeriesCollection(3).Name = TB.Range("B6")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection (4)
ActiveChart.SeriesCollection(4).XValues = TB.Range("G3:U3")
ActiveChart.SeriesCollection(4).Values = TB.Range(TB.Cells(7, 7), TB.Cells(7, 21))
ActiveChart.SeriesCollection(4).Name = TB.Range("B7")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection (5)
ActiveChart.SeriesCollection(5).XValues = TB.Range("G3:U3")
ActiveChart.SeriesCollection(5).Values = TB.Range(TB.Cells(8, 7), TB.Cells(8, 21))
ActiveChart.SeriesCollection(5).Name = TB.Range("B8")
ActiveChart.Location Where:=xlLocationAsObject, Name:=TB.Name
With ActiveChart
.HasTitle = True
.ChartTitle.Text = TB.Range("A1")
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlCategory).TickLabelSpacing = 1
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub[/VBA]

[MountainVogu]

Is this what you're trying to achieve

[VBA]Sub CreatingChart()
Dim TB As Worksheet
Dim j%, lR%
Application.ScreenUpdating = False
Set TB = ActiveSheet
lR = ActiveSheet.Range("A65536").End(xlUp).Row
Charts.Add
For j = 4 To lR
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection (j - 3)
ActiveChart.SeriesCollection(j - 3).XValues = TB.Range("G3:U3")
ActiveChart.SeriesCollection(j - 3).Values = TB.Range(TB.Cells(j, 7), TB.Cells(j, 21))
ActiveChart.SeriesCollection(j - 3).Name = TB.Range("B" & Trim(Str(j)))
Next j
ActiveChart.Location Where:=xlLocationAsObject, Name:=TB.Name
With ActiveChart
.HasTitle = True
.ChartTitle.Text = TB.Range("A1")
.ChartType = xlLineMarkers
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlCategory).TickLabelSpacing = 1
.Axes(xlValue, xlPrimary).HasTitle = False
End With
Application.ScreenUpdating = True
End Sub[/VBA]

A little more analysis and stepping through your code is needed I think.

[spflash]

MountainVogu,

Your changes in the code worked beautifully. I definitely appreciate your help on this one. I've been trying to solve this for weeks! Thanks again.

spflash