Changing many charts in one worksheet

[Libby]

I have a worksheet with over 100 similar small datatables and charts. It has
been asked of me that I now chart one more column of data from each table.
The data is there and I can record a simple macro to plot the additional
series from the first table to the first chart. My question is, how do I get
the macro to go to the next table and plot the additional data to the next
chart. The data is always in the same column, different rows.

 

[Jon Peltier]

I tested this simple macro on a worksheet with three charts. Each used data
from B to populate two series and there was additional data in E. The
macro checked that there were fewer than 3 series, and if so it copied the
formula from the second series, changed the column from D to E in the
formula, and applied the formula to a new series.

Sub ExtendOneColumn()
Const NumSeries As Long = 3
Const sOldCol As String = "$D$"
Const sNewCol As String = "$E$"

Dim iChtOb As Long
Dim Cht As Chart
Dim Srs As Series
Dim sFmla As String

For iChtOb = 1 To ActiveSheet.ChartObjects.Count
Set Cht = ActiveSheet.ChartObjects(iChtOb).Chart
With Cht.SeriesCollection
If .Count < NumSeries Then
sFmla = .Item(NumSeries - 1).Formula
Set Srs = .NewSeries
With Srs
sFmla = Replace(sFmla, sOldCol, sNewCol)
.Formula = sFmla
End With
End If
End With
Next
End Sub

- Jon