Generating Charts with VBA

Hi

I'm currently writing some code in VBA to automatically create charts in excel but I'm running into some difficulties.

The script I'm creating is generic and is used to graph two columns. The number of rows can vary on each run but both columns will always have the same number. So far I have written the following :-

Public Sub makechart(datasheet As String, xaxis As String, yaxis As String, name As String)

Dim achart As Chart
Dim lastr As Integer

lastr = lastrow(datasheet)

Set achart = Charts.Add
With achart
.ChartType = xlXYScatter

'ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "='All Data'!R2C7:R74C7"
ActiveChart.SeriesCollection(1).Values = "='All Data'!R2C8:R74C8"

End With
End Sub

The function lastrow simply returns the last row number. What I want to do is control the ranges for the x and Y values but I can't seem to get it to work. I'm looking for something like -

ActiveChart.SeriesCollection(1).XValues = "='datasheet'!xaxis &"2"::xaxis &lastr"

How do I actually script this?

Cheers

Dave

I'm trying to perform a similar task using VBA and excel to make a chart. I have several sets of data series that will go on the same chart, each set being in it's own column (xvalues in column 2, yvalues in column 3; then next series will be in columns 4 and 5 and so on). Is there a way to use variables in the range section of the code:

ActiveChart.SeriesCollection(somevariable).XValues = Worksheets("Sheet1").Range("B1:B5")

?
Maybe store the values in an array, but I haven't figured out how to go about doing that.