Hello everyone,

I use VBA create chart with multiple series line, but the legend show me seris1,series2 instead of the meaningful name(Value1,Value2).
I don't know how to change the legend name.
source data looks like this:
Year 1996 1997 1998 1999 2000 2001 2002
Value1 86738 92277 101698 116344 131771 145170 161150
Value2 0.034 0.034 0.036 0.0397636 0.043829871 0.046913567 0.050652176

here is the VBA I used to generate the chart:

Set RNG1 = Worksheets("data").Range("B3:H")
Set RNG2 = Worksheets("data").Range("B4:H4")
Set RNG3 = Worksheets("data").Range("B5:H5")

With ActiveSheet.Shapes.AddChart.Chart

.SetSourceData Source:=Union(RNG1, RNG2, RNG3)
.ChartType = xlColumnClustered
.SeriesCollection(2).ChartType = xlLine
.PlotBy = xlRows
End With


Please help.

Thanks a lot!