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
--
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

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





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

--

Dave Peterson

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.


"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

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.


"Mike H" wrote:

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.


"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

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 H" wrote:

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.


"Mike H" wrote:

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.


"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

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


"Mike H" wrote:

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.


"Mike H" wrote:

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.


"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

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.


"Jacob Skaria" wrote:

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


"Mike H" wrote:

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.


"Mike H" wrote:

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.


"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