Select all rows till last row with value in Column A and CopyPaste

  • Thread starter Thread starter PVANS
  • Start date Start date
P

PVANS

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
 
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
 
I like to use something like this:

Option Explicit
Sub myCopy()

Dim RngToCopy As Range
Dim LastRow As Long
Dim DestCell As Range

With Worksheets("Sheet1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set RngToCopy = .Rows("1:" & LastRow)
End With

With Worksheets("sheet2")
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

RngToCopy.Copy _
Destination:=DestCell

Application.CutCopyMode = False

End Sub
 
Hi,

How about this. Note we've got rid of any need to select ranges.

Sub Copy()
Dim LastrowA As Long
Dim LastrowB As Long
Set SrcSht = Sheets("Sheet1")
Set DstSht = Sheets("Sheet2")
LastrowA = SrcSht.Cells(Cells.Rows.Count, "A").End(xlUp).Row
LastrowB = DstSht.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
Range("A1:A" & LastrowA).EntireRow.Copy DstSht.Cells(LastrowB, 1)
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
oops,

use this instead

Sub Copy()
Dim LastrowA As Long
Dim LastrowB As Long
Set SrcSht = Sheets("Sheet1")
Set DstSht = Sheets("Sheet2")
LastrowA = SrcSht.Cells(Cells.Rows.Count, "A").End(xlUp).Row
LastrowB = DstSht.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
SrcSht.Range("A1:A" & LastrowA).EntireRow.Copy DstSht.Cells(LastrowB, 1)
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
Hi Mike and Barb

Thank you so much for the help! Mike, I owe you a thank you for yesterday as
well. This works perfectly.

Thanks to you both, have a good day

Regards,
Paul
 
Mike; probably you have missed to mention the source sheet object. As we are
unsure which sheet is activated; it is better to specify.

SrcSht.Range("A1:A" & LastrowA).EntireRow.Copy DstSht.Cells(LastrowB, 1)
 
Jacob,

Thanks for that i spotted it as soon as I posted and sent a correction
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
Back
Top