Changing many charts in one worksheet
 

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.

Changing many charts in one worksheet
 

I tested this simple macro on a worksheet with three charts. Each used data
from B:D 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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Libby" wrote in message
...
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.