copy contents of last row to a specific cell in another worksheet

usually you would do something like this:

Sheets("Sheet1").Range("A1")=Cells(Rows.Count,1).End(XlUp)

it will put in your Sheet1 cell A1 the value of the last row in column A.