Dynamic charts rely on dynamic range names instead of addresses in the
series formulas. There are hundreds of threads about them in this ng. John
Peltier also has a great deal of information on his site -
http://peltiertech.com/
search "dynamic charts" in the search bar on any of his pages.

You have somewhat more of a challenge as no doubt you'll want to automate
the process of making the dynamic Names and applying them to each series in
your 100 charts. Providing each set of data has a similar number of series
and each data set is similarly offset down from the set above it shouldn't
be difficult. However I don't follow your examples of "data range", it looks
like each chart has data in mulitple ranges but I wonder if that's really
the case.

If after reading up about dynamic charts you are not sure how to proceed
with automating the process of applying them, post back with details of all
the series formulas in the first chart, the first series formulas of the
next two charts and confirm each chart follows a similar pattern and each
data range similarly offset.

Of course if your charts only need to update occasionally, a quick and
simple approach would be to Replace AX with AY in each series formula, eg

Sub abc()
Dim oldCalc As Long
Dim sFmlaOld As String, sFmlaNew As String
Dim chtObj As ChartObject
Dim sr As Series

On Error GoTo done
oldCalc = Application.Calculation
Application.Calculation = xlCalculationManual

For Each chtObj In ActiveSheet.ChartObjects
For Each sr In chtObj.Chart.SeriesCollection
sFmlaOld = sr.Formula
sFmlaNew = Replace(sFmlaOld, "$AX$", "$AY$")
sr.Formula = sFmlaNew
Next
Next

done:
Application.Calculation = oldCalc
End Sub

Regards,
Peter T


"vicky" wrote in message
...
Below shows how the source data is defined for two of the charts.


Chart #1
----------------------------
Data range: =CBData!$B$57:$AX$57,CBdata!$B$61: $AX$63

Chart #2
-----------------------------
Data range: =CBdata!$B$140:$B$143,CBdata!$Q$140: $AX$143

Given this, I'd like to be able to change the ending column on all of
these
cell references to the next column. In the example above, I'd like to
change ONLY
"AX" to "AY" in all of the chart's cell references. This would have
to
happen for all 100 charts in my "chart" spreadsheet