Creating a chart with "Union"

[kotzo]

Hi,

I want to create a chart in a specific sheet ("Problematic Stock") of a workbook ("Reports_1.xls"). I take the data from the workbook ( backbone = "Data_Backbone.xls") from the sheet "Problematic Stock".

I use the following code:

  [SIZE=1]Workbooks.Open "C:\Logistics Container\Reports_1.xls"
    Workbooks("Reports_1.xls").Activate
  Worksheets("Problematic Stock").Activate[/SIZE]
[SIZE=1]   With Charts.Add
      .Name = "Test"
      .ChartType = xlColumnClustered
    [B]  .SetSourceData Source:=Workbooks(backbone).Sheet("Data_Discont").Union(range(cells(4, 1), cells(inde + 3, 1)), range(cells(4, 12), cells(inde + 3, 12))), PlotBy:=xlRows
[/B]      .HasTitle = True
      .ChartTitle.Text = "=Problematic Stock!R1C2"
      .Axes(xlCategory, xlPrimary).HasTitle = True
      .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
      .Axes(xlValue, xlPrimary).HasTitle = True
      .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Sales"
   End With[/SIZE]

Error: 438 (at the bold line).

Could you please help me ?

Thanks,

Tzovanis

 

[Andrew Poulsom]

This worked for me:

Sub Test()
    Const inde As Integer = 10
    Dim backbone As String
    Dim Sh As Worksheet
    backbone = "Data_Backbone.xls"
    Set Sh = Workbooks("Data_Backbone.xls").Worksheets("Data_Discont")
    With Charts.Add
        .SeriesCollection.NewSeries
        .SeriesCollection(1).XValues = "=[" & backbone & "]Data_Discont!" & Sh.Range(Sh.Cells(4, 1), Sh.Cells(inde + 3, 1)).Address(, , xlR1C1)
        .SeriesCollection(1).Values = "=[" & backbone & "]Data_Discont!" & Sh.Range(Sh.Cells(4, 12), Sh.Cells(inde + 3, 12)).Address(, , xlR1C1)
    End With
End Sub

 

[kotzo]

Hi,

It worked !!!!!!!!!!!!!!!!

Thank you very much for your support !!!!

Could you please explain to me why ????


Many thanks again,

Tzovanis

 

[Andrew Poulsom]

The key is:

.SeriesCollection.NewSeries

But there was also a misplaced parenthesis in the code I originally gave you. Compare the 2 lines below:

.SeriesCollection(1).XValues = "=[" & backbone & "]Data_Discont!" & Sh.Range(Sh.Cells(4, 1), Sh.Cells(inde + 3, 1).Address(, , xlR1C1))

.SeriesCollection(1).XValues = "=[" & backbone & "]Data_Discont!" & Sh.Range(Sh.Cells(4, 1), Sh.Cells(inde + 3, 1)).Address(, , xlR1C1)

 

[kotzo]

Thanks again ....