to make column letter dynamic in charts

[vicky]

I know there have been previous posts on this but I am relatively
new
to VBA and am having trouble adapting it to my needs
I have many charts in a sheet. i want to make their souce data
dynamic. the source range of charts are like sheet1!$B$9:$CN$13
where $B$9 is starting range and " $CN$ " is ending range.
for every chart the starting range is different but the ending
Datarange is of same Column "$CN" . i want to make the column Letter
dynamic ,Row number are Static ... from "CN" it has to change it to
"CO" in all the charts.. Every occurence of CN DataRange has to be
changed to CO .Every month one column gets added up. i.e from co to cp
and so on. this change has to reflect in all the charts . hope anyone
can provide a macro for this .

since there are many charts manually hardcoding using count function
is not possible...

 

[vicky]

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

 

[Peter T]

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