Graph series

Hi,



I have tried to write a macro to build the graph automatcially based on using offset series

   Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Sheets("data").Range("F5")
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).XValues = "=offset chart.xls'!chtCats"
    ActiveChart.SeriesCollection(1).Values = "=offset chart.xls'!chtValA"
    ActiveChart.SeriesCollection(1).Name = "=data!R4C2"
    ActiveChart.SeriesCollection(2).Values = "=offset chart.xls'!chtValB"
    ActiveChart.SeriesCollection(2).Name = "=data!R4C3"
    ActiveChart.Location Where:=xlLocationAsNewSheet

however i am unsure as to why it needs to pull the name of the xls, as i need this to be dynamic as every day i would like to be able to run the macro to create the graph.

Any ideas on how i get round this?
ps i have posted this to a reply to an original issue i had with have variable amounts of data when trying to create a graph. Apologies if this breaks any rules

Thanks

Antony