Help with a macro
 

Hi, I'm trying to set up a macro for a user using Excel 2003. The user has
numerous worksheets consisting of 20 to 30 "tables" of data with
corresponding charts embedded to the right of the table. They have the data
plotting correctly, but have added a new series to each table and need to
have this new series included in the existing chart objects. We've got a
macro that will plot the data for a single instance, but I haven't been able
to figure out how to get it to loop through all the tables and update all the
charts. I can manually change the cell locations and rerun the macro, but
there's got to be a better way.

Here's what I've got so far... Any suggestions would be GREATLY appreciated!

Sub AddDataSeries()
'
' AddDataSeries Macro
' Macro recorded 01/06/2007 by
'

'

ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).XValues = "={800}"
ActiveChart.SeriesCollection(2).Values = "='May 30_2007'!R248C5"
ActiveChart.SeriesCollection(2).Name = "=""SRM916"""
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).XValues = "={900}"
ActiveChart.SeriesCollection(3).Values = "='May 30_2007'!R249C5"
ActiveChart.SeriesCollection(3).Name = "=""Control 1"""
ActiveChart.SeriesCollection(4).XValues = "={1000}"
ActiveChart.SeriesCollection(4).Values = "='May 30_2007'!R250C5"
ActiveChart.SeriesCollection(4).Name = "=""Control 2"""
ActiveChart.SeriesCollection(2).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = 1
.MarkerStyle = xlSquare
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.SeriesCollection(2).AxisGroup = 2
ActiveChart.SeriesCollection(3).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = 1
.MarkerStyle = xlTriangle
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.SeriesCollection(3).AxisGroup = 2
ActiveChart.SeriesCollection(4).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
With Selection
.MarkerBackgroundColorIndex = 1
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlX
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.SeriesCollection(4).AxisGroup = 2
ActiveChart.Axes(xlValue, xlSecondary).Select
With ActiveChart.Axes(xlValue, xlSecondary)
.MinimumScaleIsAuto = True
.MaximumScale = 3
.MinorUnitIsAuto = True
.MajorUnit = 1
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveChart.SeriesCollection(4).Select
ActiveChart.SeriesCollection(4).ApplyDataLabels AutoText:=True,
LegendKey:= _
False, ShowSeriesName:=True, ShowCategoryName:=False,
ShowValue:=False, _
ShowPercentage:=False, ShowBubbleSize:=False
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).ApplyDataLabels AutoText:=True,
LegendKey:= _
False, ShowSeriesName:=True, ShowCategoryName:=False,
ShowValue:=False, _
ShowPercentage:=False, ShowBubbleSize:=False
ActiveChart.SeriesCollection(3).Select
ActiveChart.SeriesCollection(3).ApplyDataLabels AutoText:=True,
LegendKey:= _
False, ShowSeriesName:=True, ShowCategoryName:=False,
ShowValue:=False, _
ShowPercentage:=False, ShowBubbleSize:=False

End Sub

Thanks!