Change Chart Series with VBA

[Sueann]

Can anyone help? I am new to VBA

I have several chart sheets (they are not embedded) that I would lik
to change the series using vba instead of perfoming manually each tim
the data range changes.

I have found the following code from the link below to change char
series.
http://peltiertech.com/Excel/Charts/ChgSrsFmla.html

with the code:


Code
-------------------
Sub ChangeSeriesFormulaAllCharts()
''' Do all charts in sheet
Dim oChart As ChartObject
Dim OldString As String, NewString As String
Dim mySrs As Series

OldString = InputBox("Enter the string to be replaced:", "Enter old string")

If Len(OldString) > 1 Then
NewString = InputBox("Enter the string to replace " & """" _
& OldString & """:", "Enter new string")
For Each oChart In ActiveSheet.ChartObjects
For Each mySrs In oChart.Chart.SeriesCollection
mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, OldString, NewString)
Next
Next
Else
MsgBox "Nothing to be replaced.", vbInformation, "Nothing Entered"
End If
End Su
-------------------


However, does anyone have any code that automatically change the char
series(when the data range changes) without using input/message boxes?

Many thank

 

[Jon Peltier]

You don't need VBA for this kind of dynamic chart behavior. You can set up
dynamic data ranges that update their definitions when the amount of data
changes, and use these ranges for the chart source data. Here are a few
examples and links to more:

http://www.peltiertech.com/Excel/Charts/Dynamics.html

- Jon

 

[Sueann]

Thanks for the link Jon, my charts are now updating automatically.