Try this

Option Explicit

Sub Test()

Dim Lastrow As Long
Dim myWS1 As Excel.Worksheet
Dim myWS2 As Excel.Worksheet

On Error Resume Next
Set myWS1 = Worksheets("Sheet1")
Set myWS2 = Worksheets("Sheet2")
On Error GoTo 0

If myWS1 Is Nothing Or _
myWS2 Is Nothing Then
MsgBox ("One of your worksheets does not exist.")
Exit Sub
End If

Lastrow = myWS2.Cells(myWS2.Rows.Count, "A").End(xlUp).Row
myWS1.Rows("1:250").Copy Destination:= _
myWS2.Cells(Lastrow + 1, 1)

End Sub
--
HTH,

Barb Reinhardt



"PVANS" wrote:

Good morning

Was wondering whether someone could help me with this.
I am trying to select all rows up until the last row with a value in column
A on Worksheet1, copy this selection, and paste it into the (last row + 1) in
Worksheet 2. Initially I was going to use this code below (as I know that
the total number of lines on sheet1 will never exceed 250):

Sub Copy()

Dim Lastrow As Long

Sheets("Sheet1").Select
Rows("1:250").Select
Selection.Copy
Sheets("Sheet2").Select
Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Cells(Lastrow + 1, 1).Select
Selection.Paste

End Sub

I get the following error:
Run-time error '438':
Object doesn't support this property or method

Please can someone assist? (also, whilst the code above simply selects
columns1:250, if I could select up until the last row with data in column A,
I would prefer that)

Thanks so much for the help