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

March 19th, 2010 - 07:10 am ET by PVANS | Report spam

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

Follow the discussion Replies

7 replies

Make a reply

Replies

#1 Barb Reinhardt

March 19th, 2010 - 07:27 am ET | Report spam

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

Reply to this message

#2 Dave Peterson

March 19th, 2010 - 07:36 am ET | Report spam

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

Reply to this message

#3 Mike H

March 19th, 2010 - 07:55 am ET | Report spam

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

Reply to this message

#4 Mike H

March 19th, 2010 - 07:58 am ET | Report spam

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

Reply to this message

#5 PVANS

March 19th, 2010 - 08:11 am ET | Report spam

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

Reply to this message

Create a new topicNext page

Make a reply

Make your own search

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

Similar topics

Replies