How to Set Range of Table Data for use with Chart

Question

Using Excel 2010 I am trying to create a chart on Sheet1 using partial data from several different tables. In the below example I am just trying to pull the data from one table as a start. I seem to be having difficulty setting the range of this data in this table.

I will eventually loop through about 8 data columns of various lengths per table with approx. 21 tables. So any help automating this would be great

This is what I have so far...

Sub createChart()
    Dim rng As Range
    Dim chrt As Object

    'Table is named BMS_01_01_Module_01 and the column Name I want data from is Cell 1 Voltage
    Set chrt = Sheet1.Shapes.AddChart2
    chrt.ChartArea.SetSourceData Source:=activesheetlistobjects("BMS_01_01_Module_01").ListColumns("Cell 1 Voltage").Range
    chrt.ChartArea.ChartType = x1Line
End Sub

score 0 · Answer 1 · answered Oct 10 '18 at 21:56

I assume that the code above is producing "Object Required" error. I would recommend splitting the statement (ChartArea.SetSourceData) into multiple steps which will give you more control on the code and flexibility.

To reference table ranges just create a ListObject variable and a Range variable, rest is explained below with examples.

Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("BMS_01_01_Module_01")

As using so, one will be able to reference any part of the table really easily:

Dim rng As Range

'Reference the whole table, including headers:
Set rng = tbl.Range

'Reference just the table data (no headers):
Set rng = tbl.DataBodyRange

'Reference just the data in a single column:
Set rng = tbl.ListColumns("Cell 1 Voltage").DataBodyRange

'Reference the headers only
Set rng = tbl.HeaderRowRange

Hence your code should be something similar as below:

chrt.ChartArea.SetSourceData Source:=tbl.ListColumns("Cell 1 Voltage").DataBodyRange

score 0 · Answer 2 · answered Oct 11 '18 at 01:43

Try this.

Sub createChart()
    Dim rng As Range
    Dim obj As ChartObject
    Dim chrt As Chart
    Dim Lst As ListObject
    Dim cm  As ListColumn
    Dim Ws As Worksheet

    Set Ws = ActiveSheet
    Set Lst = Ws.ListObjects("BMS_01_01_Module_01")
    Set cm = Lst.ListColumns("Cell 1 Voltage")
    'Set cm = Lst.ListColumns(1)
    Set rng = cm.DataBodyRange

    'Table is named BMS_01_01_Module_01 and the column Name I want data from is Cell 1 Voltage
    Set obj = Sheet1.ChartObjects.Add(200, 200, 400, 400)
    With obj.Chart
    'chrt.ChartArea.SetSourceData Source:=ActiveSheet.ListObjects("BMS_01_01_Module_01").ListColumns("Cell 1 Voltage").Range
        .SetSourceData Source:=rng
        .ChartType = xlLine
    End With
End Sub

score 0 · Accepted Answer · answered Oct 11 '18 at 13:21

I ended up making some changes to my code. I just wish there was a way to reference the table without having to activate the sheet the table is in first. Note: the Chart and Tables are not on the same sheets.

This is what ended up working for a single range:

Sub createChart()
Dim rng As Range
Dim cht As Object
Dim Ws As Worksheet

Set cht = Sheet1.Shapes.addChart

Worksheets("BMS_01_Module_01").Activate
Set Ws = ActiveSheet

Ws.ListObjects("BMS_01_Module_01").ListColumns("Cell 1 Voltage").Range.Select
cht.Chart.SetSourceData Source:=Selection
cht.Chart.ChartType = xlLine


End Sub

How to Set Range of Table Data for use with Chart

3 Answers3