change data series for multiple charts

[erickguz]

Hello - I found this VBA that changes the data series for all of the charts in my workbook. Works great.

1. How do I change so that it only changes the charts on a sheet, and not the entire workbook...

2. Possible to also only change selected charts?

Thank you.


Sub ChangeAllCharts()
Dim sht As Worksheet
Dim cht As ChartObject
Dim ser As Series

For Each sht In ActiveWorkbook.Worksheets
For Each cht In sht.ChartObjects
For Each ser In cht.Chart.SeriesCollection
ser.Formula = Replace(ser.Formula, "$D$5:$D$26", "$D$5:$D$27")
Next ser
Next cht
Next sht
End Sub

 

[AlphaFrog]

[color=darkblue]Sub[/color] ChangeAllChartsOnActiveSheet()
    [color=darkblue]Dim[/color] cht       [color=darkblue]As[/color] ChartObject
    [color=darkblue]Dim[/color] ser       [color=darkblue]As[/color] Series


    [color=darkblue]For[/color] [color=darkblue]Each[/color] cht [color=darkblue]In[/color] ActiveSheet.ChartObjects
        [color=darkblue]For[/color] [color=darkblue]Each[/color] ser [color=darkblue]In[/color] cht.Chart.SeriesCollection
            ser.Formula = Replace(ser.[color=darkblue]For[/color]mula, "$D$5:$D$26", "$D$5:$D$27")
        [color=darkblue]Next[/color] ser
    [color=darkblue]Next[/color] cht
[color=darkblue]End[/color] [color=darkblue]Sub[/color]


[color=darkblue]Sub[/color] ChangeAllChartsSelected()
    [color=darkblue]Dim[/color] cht       [color=darkblue]As[/color] ChartObject
    [color=darkblue]Dim[/color] ser       [color=darkblue]As[/color] Series


    [color=darkblue]For[/color] [color=darkblue]Each[/color] cht [color=darkblue]In[/color] Selection
        For [color=darkblue]Each[/color] ser [color=darkblue]In[/color] cht.Chart.SeriesCollection
            ser.Formula = Replace(ser.Formula, "$D$5:$D$26", "$D$5:$D$27")
        [color=darkblue]Next[/color] ser
    [color=darkblue]Next[/color] cht
[color=darkblue]End[/color] Sub

 

[erickguz]

Excellent AlphaFrog - thank you. How time saving!!