need help adding named range as new series to a graph

[Keith R]

I have a graph with up to 6 series. I need to dynamically add and remove
series depending on how what data is present.I don't know how to do it well,
so I am deleting all but one existing series and adding back one series at a
time until I get the right number I need.

All my series are set to named ranges, but I'm having trouble with the VBA
syntax to add a named range as a new series. Below is some edited code I
have so far, originally based on a recorded macro; I don't think it is
copying the named range to add it as a new series. Optimally, I'd like to
add a new series and have it (in the data source) refer directly to the
named range, rather than the specific cells in that range, so I can change
the graph by changing the named range if needed.
Any help greatly appreciated,
keith

Sub resetseries()

On Error Resume Next
ActiveSheet.ChartObjects("Chart 22").Activate
ActiveChart.SeriesCollection(6).Select
Selection.Delete
ActiveChart.SeriesCollection(5).Select
Selection.Delete
ActiveChart.SeriesCollection(4).Select
Selection.Delete
ActiveChart.SeriesCollection(3).Select
Selection.Delete

TotalSeries = (Sheet1.Range("B8").Value)
If TotalSeries > 1 Then
For j = TotalSeries To 1 Step -1
AddEachSeries = Choose(j, Act1, Act2, Act3, Act4, Act5)
AddEachSeries.Copy
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection.Paste Rowcol:=xlColumns,
SeriesLabels:=False, _
CategoryLabels:=False, Replace:=False, NewSeries:=True
ActiveChart.SeriesCollection(SeriesNum).Select
Application.CutCopyMode = False
ActiveChart.SeriesCollection(SeriesNum).ChartType =
xlColumnClustered
Next
End If
End Sub

 

[Jon Peltier]

Keith -

You've omitted some details. Your variable declarations, for instance.
I don't know what Act1 through Act5 are, or even what type they are.

You don't need to copy ranges and paste them into the chart. Something
like this would work better.

Jmax = Sheet1.Range("B8").Value
SrsMax = activechart.seriescollection.count
For j = SrsMax to 1 step -1
If j > Jmax then
activechart.seriescollection(j).delete
else if j > SrsMax then
activechart.seriescollection.NewSeries
activechart.seriescollection(j).values = _
Sheet1.Range("Act1")
else
activechart.seriescollection(j).values = _
Sheet1.Range("Act1")
end if
Next

This assumes the name of the range in the worksheet is "Act1". You
could also use
Sheet1.Range("Act" & j)
if the range names are regularly named.

- Jon