Chart series don't extend

[JackkG]

Hi,

I'm trying to extend the chart according to new added values. Here is the sample code i have recorded through a macro, but it doesn't seem to work when i run the code? Any help or tweak to the code is much appreciated. Thanks in advance!

Sub Chartext()

If ThisWorkbook.Worksheets("Sum").Range("A2").Value = 5 Then
ThisWorkbook.Worksheets("Schart").ChartObjects("Chart 1").Activate
    Application.CutCopyMode = False
    ActiveChart.FullSeriesCollection(1).XValues = "=Schart!$L$345:$Z$345"
    ActiveChart.FullSeriesCollection(1).Values = "=Schart!$L$346:$Z$346"
    ActiveChart.FullSeriesCollection(2).Values = "=Schart!$L$348:$Z$348"
    ActiveChart.FullSeriesCollection(3).Values = "=Schart!$L$356:$Z$356"
    ActiveChart.FullSeriesCollection(4).Values = "=Schart!$L$332:$Z$332"
    ActiveChart.FullSeriesCollection(5).Values = "=Schart!$L$350:$Z$350"
End If
End Sub

The same has been posted here:
https://www.excelforum.com/excel-cha...ml#post5130022

[Aussiebear]

Aren't you already receiving assistance on this subject in another forum?

[p45cal]

I think you may need to set the XValues for all the series:

With ActiveSheet.ChartObjects("Chart 1").Chart
  .FullSeriesCollection(1).Values = "=Sheet1!$I$22:$U$22" 'I've used columns I:U - adjust yourself.
  .FullSeriesCollection(2).Values = "=Sheet1!$I$23:$U$23"
  .FullSeriesCollection(3).Values = "=Sheet1!$I$24:$U$24"
  .FullSeriesCollection(4).Values = "=Sheet1!$I$25:$U$25"
  .FullSeriesCollection(5).Values = "=Sheet1!$I$26:$U$26"
  .FullSeriesCollection(1).XValues = "=Sheet1!$I$21:$U$21"
  .FullSeriesCollection(2).XValues = "=Sheet1!$I$21:$U$21"
  .FullSeriesCollection(3).XValues = "=Sheet1!$I$21:$U$21"
  .FullSeriesCollection(4).XValues = "=Sheet1!$I$21:$U$21"
  .FullSeriesCollection(5).XValues = "=Sheet1!$I$21:$U$21"
End With

Alternatives:

With ActiveSheet.ChartObjects("Chart 1").Chart
  .FullSeriesCollection(1).Values = Range("$D$22:$X$22")
  .FullSeriesCollection(2).Values = Range("$D$23:$X$23")
  .FullSeriesCollection(3).Values = Range("$D$24:$X$24")
  .FullSeriesCollection(4).Values = Range("$D$25:$X$25")
  .FullSeriesCollection(5).Values = Range("$D$26:$X$26")
  .FullSeriesCollection(1).XValues = Range("$D$21:$X$21")
  .FullSeriesCollection(2).XValues = Range("$D$21:$X$21")
  .FullSeriesCollection(3).XValues = Range("$D$21:$X$21")
  .FullSeriesCollection(4).XValues = Range("$D$21:$X$21")
  .FullSeriesCollection(5).XValues = Range("$D$21:$X$21")
End With

or:

Set SceDataRng = Range("D21").CurrentRegion
With ActiveSheet.ChartObjects("Chart 1").Chart
  .FullSeriesCollection(1).XValues = SceDataRng.Rows(1) 
  .FullSeriesCollection(2).XValues = SceDataRng.Rows(1)
  .FullSeriesCollection(3).XValues = SceDataRng.Rows(1)
  .FullSeriesCollection(4).XValues = SceDataRng.Rows(1)
  .FullSeriesCollection(5).XValues = SceDataRng.Rows(1)
  .FullSeriesCollection(1).Values = SceDataRng.Rows(2)
  .FullSeriesCollection(2).Values = SceDataRng.Rows(3)
  .FullSeriesCollection(3).Values = SceDataRng.Rows(4)
  .FullSeriesCollection(4).Values = SceDataRng.Rows(5)
  .FullSeriesCollection(5).Values = SceDataRng.Rows(6)
End With

or:

Set SceDataRng = Range("D21").CurrentRegion
With ActiveSheet.ChartObjects("Chart 1").Chart
  For i = 1 To 5
    .FullSeriesCollection(i).XValues = SceDataRng.Rows(1)
    .FullSeriesCollection(i).Values = SceDataRng.Rows(i + 1)
  Next i
End With

(using the attachment at ExcelForum)

[JackkG]

Thanks, p45cal!!! It worked perfectly fine. Thanks a lot for your help!!