I always distrust End(xlUp) because there may be an empty cell in that
column. I use the following to select the last row of data (assuming the
list starts at the top left of your sheet but you could change A1 to
something else):

Range("A1").CurrentRegion.Select
Selection.Offset(Selection.Rows.Count - 1).Resize(1).Select

Rather than a recordset - which, as you say is a bit "Accessy" - why not
store this range of values into an array:

Dim myArray As Variant
Range("A1").CurrentRegion.Select
myArray = Selection.Offset(Selection.Rows.Count - 1).Resize(1)

The nice thing about using a variant array is that you can populate it with
the single step shown rather than having to write a loop. The array is
two-dimensional so the first cell's value is stored in myArray(1,1), second
in myArray(1,2), etc.

"Callan" wrote:

Please can someone assist me with this as I usually program in access and I'm
struggling with the VB in excel.

From a button_click procedure, I need to set the last row inserted in
"Sheet1" as a recordset in order to use the different values in various cells
in "Sheet2".
e.g Dim rs as recordset in Access.

I would like to say something to the effect of:
Find the last record inserted (the last row on the sheet):
eg: Set rRng = Range("A3:A" & Range("A" & Rows.Count).End(xlUp).Row), but
for the entire row.
Then I need to be able to say something like:
Set Sheet2 C10 = rs!sheet1 B3

ANY assistance would be greatly appreciated.
Thanks in advance.