[RESOLVED] Setting Chart source to a Range fails.

[JimOldguy]

I have this code:

With ActiveWorkbook.ActiveSheet
                .Cells(1, 1) = "Time"
                .Cells(1, 2) = "Temperature"
                .Cells(1, 3) = "Target"
                .Name = Format(Now, "dd-MMM-yyyy")
                .ListObjects.Add(xlSrcRange, Range("A1:C2"), , xlYes).Name = "Table1"
                Set cht = ActiveSheet.ChartObjects.Add(Left:=ActiveSheet.Cells(2, 5).Left, Width:=750, Top:=ActiveSheet.Cells(2, 5).Top, Height:=250)
                With cht
                    .Chart.ChartType = xlXYScatterLines
                    .Chart.SetSourceData ActiveSheet.Range("Table1[#All]")
                    .Chart.SetSourceData ActiveSheet.Range("A1:C2")
                    .Chart.HasTitle = True

The first line that tries .Chart.SetSourceData always fails, so I have to comment it out, and allow the second line instead.

I see:
Run-time error "1004"
"Application-defined or object-defined error."

I am trying to ensure the chart automatically updates when new data is added to the Table1 cells, but unsuccessfully!

Any help would be much appreciated.

[Zvoni]

Have you tried the UsedRange-Property instead?

.Chart.SetSourceData ActiveSheet.UsedRange

[JimOldguy]

Found an answer!

    .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = Format(Now, "dd-MMM-yyyy")
            With ActiveSheet
                .Cells(1, 1) = "Time"
                .Cells(1, 2) = "Temperature"
                .Cells(1, 3) = "Target"
                .ListObjects.Add(xlSrcRange, Range("A1:C2"), , xlYes).Name = "Table"
                Set cht = ActiveSheet.ChartObjects.Add(Left:=ActiveSheet.Cells(2, 5).Left, Width:=750, Top:=ActiveSheet.Cells(2, 5).Top, Height:=250)
                With cht
                    .Chart.ChartType = xlXYScatterLines
                    .Chart.SetSourceData Source:=Range("Table[#All]")
                    .Chart.HasTitle = True

This actually works!
Thanks.

[JimOldguy]

Found an answer!

    .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = Format(Now, "dd-MMM-yyyy")
            With ActiveSheet
                .Cells(1, 1) = "Time"
                .Cells(1, 2) = "Temperature"
                .Cells(1, 3) = "Target"
                .ListObjects.Add(xlSrcRange, Range("A1:C2"), , xlYes).Name = "Table"
                Set cht = ActiveSheet.ChartObjects.Add(Left:=ActiveSheet.Cells(2, 5).Left, Width:=750, Top:=ActiveSheet.Cells(2, 5).Top, Height:=250)
                With cht
                    .Chart.ChartType = xlXYScatterLines
                    .Chart.SetSourceData Source:=Range("Table[#All]")
                    .Chart.HasTitle = True

This actually works!
Thanks.

[JimOldguy]

Now I have a new problem.
I need to be able to name the new table, on each new sheet, uniquely, so the chart then references the correct data.
Currently, the data named "Table" refers to the sheet on which it was created.
I've tried naming the new data range according to the date, i.e. "28-01-2019", which works, and creates the new range OK, but then I cannot use the .Chart.SetSourceData command!
It may just be my syntax:

.ListObjects.Add(xlSrcRange, Range("A1:C2"), , xlYes).Name = Format (Now, "dd-mm-yyyy")
rangename$=Format (Now, "dd-mm-yyyy")


.Chart .SetSourceData Source:=Range("" & rangename$ & "[#All]")

This throws up the error:Runtime error "1004" Method 'Assistant' of object '_Global' failed.

Can anyone throw some light on this, please?

Alternatively, can the SetSourceData command be set to point to the active sheet?

[JimOldguy]

Well, after a fair bit of fiddling, I finally got it to work!

                .ListObjects.Add(xlSrcRange, Range("A1:C2"), , xlYes).Name = "Table" & Sheets.Count
                Set cht = ActiveSheet.ChartObjects.Add(Left:=ActiveSheet.Cells(2, 5).Left, Width:=750, Top:=ActiveSheet.Cells(2, 5).Top, Height:=250)
                With cht.Chart
                    .ChartType = xlXYScatterLines
                    .SetSourceData Source:=Range("Table" & Sheets.Count & "[#All]")

It was just a matter of getting the syntax correct!