vba dynamically add series to chart in Excel

Q179. In Excel, how can I dynamically add series to an existing chart using VBA?

For example, I already have a chart ("Chart1") of Sales data for Q1-2016. When Q2 Sales data becomes available how do I add it to an existing chart using VBA?

excel vba dynamically add series to chart

To do it in Excel, here is the answer:

  1. Option Explicit
  2. Sub AddChartSeries()
  3. With ActiveSheet.ChartObjects("Chart1").Chart.SeriesCollection.NewSeries
  4. .XValues = ActiveSheet.Range("BU5:BU9")
  5. .Values = ActiveSheet.Range("BW5:BW9")
  6. .Name = ActiveSheet.Range("BW4")
  7. End With
  8. End Sub

Description:

a) Line 3 - Adds a new series with range specified in "Line 4 for X-Axis Labels", "Line 5 for Y-Axis Values" and "Line 6 for Series Name".

Result after Macro execution:

excel excel vba dynamically add series to chart

 

You can find similar Excel Questions and Answer hereunder

1) How can I delete all shapes in a WorkSheet?

2) How do I assign a macro to a shape using VBA?

3) Excel 2010 vba replacement of application filesearch in Excel

4) How can I activate a routine when there is a change in value of a cell?

5) How can I fill a series of data automatically?

6) How can I add a Prefix to a set of numbers in a range?

7) How can I list all files in a folder using VBA?

8) How can worksheet functions be accessed in VBA?

9) Vba to return week numbers in Excel

10) How can I export a chart as a gif file?

11) How can I hide a sheet completely from users (the sheet should not even appear in Unhide dialog box)?

12) How can I save a WorkSheet as a new WorkBook using VBA?

13) How can I add a Footer to my printout with automatic page numbering?

14) How can I loop through all ActiveX checkboxes in WorkSheet and set them to Unchecked status?

15) How do I enter a formula in a cell using VBA (using Absolute Reference)?