Using a variable to select data for plotting

Hey all,

I am creating a macro that would access a number of workbooks and plot similar graphs in all of them. I am looking to use the bit of code generated by Excel, that I have included below, in my Macro.

ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
    ActiveChart.SetSourceData Source:=Range("'1'!$A$7:$A$64,'1'!$U$7:$U$64")

My problem now is that the number of rows differ in each workbook. So I have added the following to count the number of rows:

lr = Cells(Rows.Count, "B").End(xlUp).Row

Now I would like to use the variable lr in the code that creates the plot (to replace 64), but I am unsure of how to do this.

Any help would be really appreciated.

Thanks in advance

Lovi