Excel 2010 VBA.. Adding series to existing chart

Question

I am currently trying to populate a chart with data where the user can simply press the button on the spreadsheet. The issue I am having is I need the button to copy data to a data sheet and then the chart will populate from the data sheet. I can do this, but I need a new series created on the chart for every new data that is ente

Sub RoundedRectangle2_Click()

End Sub


Sub MAPS()
Sheets("MAPS_FORM").Range("e47").Copy
Sheets("Data_Sheet").Range("b65536").End(xlUp).Offset(1, 0).PasteSpecial _
Paste:=xlPasteValues
Application.CutCopyMode = False
Sheets("MAPS_FORM").Range("d2").Copy
Sheets("Data_Sheet").Range("a65536").End(xlUp).Offset(1, 0).PasteSpecial _
Paste:=xlPasteValues
Application.CutCopyMode = False



Sheets("Data_Sheet").Range("b2:b46").Copy
Charts("Chart1").SeriesCollection.Paste

Range("e6:i8").ClearContents
Range("e12:i19").ClearContents
Range("e23:i27").ClearContents
Range("e31:i36").ClearContents
Range("e40:i43").ClearContents
Range("d2").ClearContents
Sheets("Data_Sheet").Select
End Sub

Siddharth Rout Siddharth Rout · Accepted Answer · 2013-02-15T15:54:13

Simply change the .SetSourceData property after you add the data.

Let's take an example

Lets say you data is from A1:A5 and the chart is based on that range. See screenshot below

enter image description here

Now lets say your added data is from B1:B5 and want that to show as a series in the chart then simply use this code

Option Explicit

'~~> Please amend the code as applicable
Sub Sample()
    Dim objChrt As ChartObject
    Dim chrt As Chart

    Set objChrt = ActiveSheet.ChartObjects("Chart 1")
    Set chrt = objChrt.Chart

    With chrt
        .SetSourceData (ActiveSheet.Range("A1:B5"))
    End With
End Sub

When you run the code the chart will automatically show the new series.

enter image description here

Excel 2010 VBA.. Adding series to existing chart

1 Answers