Excel VBA adding data to a chart

Question

Hello I have a little question for adding data to an existing chart.

Now I have a worksheet containing a data series with months for the years in the 2nd row of the sheet. So the months are for example B2 1.2017, C2 2.2017, and in the rows 3,4,5,6,7 and 8 there is always data for that month.

Now I just want my macro to add the new Month plus the data of the rows below to my existing chart.

the code I have so far is this:

Worksheets("Summary").ChartObjects("Chart").Activate
   ActiveChart.SeriesCollection.Add _
   Source:=Worksheets("Summary").Range("B2:B8")

now this does just create new data series but there is actually no new data added to the chart.

Shai Rado · Accepted Answer · 2017-11-23 11:43:36Z

3

The code below might seem a little long, but it's the safest way to add a new Series with Data to an existing Chart.

I'm setting all the necessary Objects so the code will be as "safe-proof" as can be.

Code

Option Explicit

Sub AddSeriestoChart()

Dim ws As Worksheet
Dim ChtRng As Range
Dim ChtObj As ChartObject
Dim Ser As Series

' set the Worksheet object
Set ws = ThisWorkbook.Worksheets("Summary")

' Set the Chart Object
Set ChtObj = ws.ChartObjects("Chart")

' Set the Range of the Chart's source data
Set ChtRng = ws.Range("B2:B8")

With ChtObj
    ' add a new series to chart
    Set Ser = .Chart.SeriesCollection.NewSeries

    ' set the source data of the new series
    Ser.Values = "=" & ChtRng.Address(False, False, xlA1, xlExternal)
End With

End Sub

Edit 1: to modify existing Series data, use something like the code below :

With ChtObj          
    For i = 1 To .Chart.SeriesCollection.Count
        Set Ser = .Chart.SeriesCollection(i)

        ' set the source data of the new series
        Set ChtRng = ws.Range("B" & i + 2)
        Ser.Values = "=" & ChtRng.Address(False, False, xlA1, xlExternal)

        Set ChtRng = Nothing
    Next i
End With

edited Nov 23, 2017 at 11:43

answered Nov 23, 2017 at 11:23

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

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

6 Comments

webjoh Over a year ago

now it does actually insert the data into the table but into a new series, which is not what I want. I want the data to be inserted into an existing series for example Cell b3 into series 1 and cell b4 into series 2

Shai Rado Over a year ago

@webjoh see edited code under Edit 1, you might need to tweak it a bit to fit your needs

webjoh Over a year ago

Okey this does delete everything that has been in the series before, unfortunately. But I would actually just need to add this to the exsiting series. I will give you an example of my table: 1.2017 turnover 50; 2.2017 turnover 60 now I just want the macro to add the data for the turnover for 3.2017 when I inserted the data and not delete everything else, as the data is not being put in once, but every month.

Jon Peltier Over a year ago

You can just use Ser.Values = ChtRng without generating the address.

Shai Rado Over a year ago

@JonPeltier for some reason I have seen times that it's not updating the chart , don't know why exactly. So I have been using this method lately

|

Mr.Burns · Accepted Answer · 2017-11-23 10:23:54Z

This is what I would use

wsMetric.ChartObjects("Chart").Chart

'This one will link data from another workbook
.SeriesCollection(1).Values = "='[" & wb.Name & "]" & ws.Name & "'!$" & sCol & "$" & lRow  & ":$" & sCol2 & "$" & lRow2 
'Debug.Print "='[" & wb.Name & "]" & ws.Name & "'!$" & sCol & "$" & lRow  & ":$" & sCol2 & "$" & lRow2 'Returns ='[Book1.xlsm]Sheet1'!$A$1:$A$11


'This one will link data from the same workbook, same or different sheet
.SeriesCollection(1).Values = "=" & ws.Name & "!$" & sCol & "$" & lRow & ":$" & sCol2 & "$" & lRow 2
'Debug.print "=" & ActiveSheet.Name & "!$" & scol & "$" & lrow & ":$" & scol2 & "$" & lrow2 'Returns =Sheet1!$A$1:$A$11
End With

This doesn't use .Activate and directly accesses the chart

Collectives™ on Stack Overflow

Excel VBA adding data to a chart

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related