Problem Creating Chart with Variables in Range

[kotzo]

Hi,

I have the following data:

Workbook "Data_Backbone.xls"
Worksheet "Data_Discont"

X -Axis Y-Axis

12.9.2010 100
13.9.2010 200
14.9.2010 300

with several columns in between

I want to create the chart in

Workbook "Reports_1.xls"
Worksheet "Problematic Stock"


The following code, is creating several series and not one series
with the above data. What is the mistake ?

[SIZE=1]Workbooks.Open "C:\Logistics Container\Reports_1.xls"[/SIZE]
[SIZE=1] Workbooks("Reports_1.xls").Activate[/SIZE]
[SIZE=1]Worksheets("Problematic Stock").Activate[/SIZE]
[SIZE=1]Set Sh = Workbooks(backbone).Sheets("Data_Discont")[/SIZE]
[SIZE=1]With Charts.Add[/SIZE]
 
[SIZE=1]   .ChartType = xlLineMarkers[/SIZE]
[SIZE=1]    .SetSourceData Source:=Application.Union(Sh.Range(Sh.Cells(4, 1), Sh.Cells(inde + 3, 1)), Sh.Range(Sh.Cells(4, 12), Sh.Cells(inde + 3, 12))), PlotBy:=xlRows[/SIZE]
 
[SIZE=1]   .HasTitle = True[/SIZE]
[SIZE=1]    .HasLegend = False[/SIZE]
[SIZE=1]    .ChartTitle.Text = "Discontinued Products"[/SIZE]
[SIZE=1]   .Axes(xlCategory, xlPrimary).HasTitle = False[/SIZE]
[SIZE=1]   .Axes(xlValue, xlPrimary).HasTitle = True[/SIZE]
[SIZE=1]   .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Value"[/SIZE]
[SIZE=1]   .Location Where:=xlLocationAsObject, Name:="Problematic Stock"[/SIZE]

follwing tried alternative creates the same error

[SIZE=1].SeriesCollection.Add Source:=Application.Union(Sh.Range(Sh.Cells(4, 1), Sh.Cells(inde + 3, 1)), Sh.Range(Sh.Cells(4, 12), Sh.Cells(inde + 3, 12))), Rowcol:=xlRows[/SIZE]

Could you please help me ?

Tzovanis

 

[kotzo]

Hi,

could you please help me ?

Tzovanis