VBA to update Chart Source Data Rage on Multiple Charts

[MikeZz]

What I'm trying to do is to have a macro automatically change the number of
series and categories on a series of charts. Each chart refers to a
different data sheet.

I recorded a macro to delete the data range in each chart, then copy a
dynamic range from the data sheet and pasted it into the chart. This code
currently dies on the ActiveChart.Delete line. When I recorded the macro, it
created that line when I went into chart>SourceData and deleted the
DataRange. When I did that, it seemed to keep the chart structure in place,
just removed the data.

I'll paste the exact recorded macro below my code for reference.

Thanks for any help!
MikeZz

Basic background info:
Charts are all on sheet "Report".
Charts are named "ChartA", "ChartB", etc...
Data for ChartA is on sheet "A", Data for ChartB is on sheet "B"
Each data sheet (A,B, etc) have a range called "ChartData"

Sub My_Modified_Code()
Dim oChart As ChartObject
Dim cht As Object, sh As Worksheet

For Each oChart In ActiveSheet.ChartObjects
chtName = oChart.Name
chtSheet = Replace(chtName, "Chart", "")

ActiveSheet.ChartObjects(chtName).Activate
ActiveChart.ChartArea.Select
Application.CutCopyMode = False
ActiveChart.Delete
ActiveWindow.Visible = False
Windows("Measurables Chart 8-Panel v7.xls").Activate
Sheets(chtSheet).Select
Application.Goto Reference:="ChartData"
Selection.Copy
Sheets("Report").Select
ActiveSheet.ChartObjects(chtName).Activate
ActiveChart.SeriesCollection.Paste Rowcol:=xlColumns,
SeriesLabels:=True, _
CategoryLabels:=True, Replace:=True, NewSeries:=True

Next

End Sub


sub Recorded_Macro
ActiveSheet.ChartObjects("ChartA").Activate
ActiveChart.ChartArea.Select
Application.CutCopyMode = False
ActiveChart.Delete
ActiveWindow.Visible = False
Windows("Measurables Chart 8-Panel v7.xls").Activate
Sheets("B").Select
Application.Goto Reference:="ChartData"
Selection.Copy
Sheets("Report").Select
ActiveSheet.ChartObjects("ChartA").Activate
ActiveChart.SeriesCollection.Paste Rowcol:=xlColumns,
SeriesLabels:=True, _
CategoryLabels:=True, Replace:=True, NewSeries:=True
end sub

 

[Jon Peltier]

Mike -

See my response in the other newsgroup.

- Jon