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 · Accepted Answer · 2013-02-15 15:54:13Z

1

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

answered Feb 15, 2013 at 15:54

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

kevin king Over a year ago

Thanks for the response. I'll have to check it out and try it on Monday to see if I can get it to work for me.

kevin king Over a year ago

What i am looking to do is every time the button is clicked a new set of data is copied into the Data_Sheet which is then entered into the chart as a new series based on the series name being the date and the series data point being a number.

Siddharth Rout Over a year ago

That's what the code does. simply change the SetSourceData after you enter th new data in the sheet.

kevin king Over a year ago

So this code needs to be in it's own module? Forgive my ignorance I don't work too much with VBA and most of my experience is trial and error.

Siddharth Rout Over a year ago

Lets say you add the data to Col C then your sourcedata in the above code will become A1:C5

|

Collectives™ on Stack Overflow

Excel 2010 VBA.. Adding series to existing chart

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related