vba copy row problem

[dnessim]

I trying to get this code to run, its just a sample for learning purposes

can you tell me why I would get a

"runtime error 424 object required" on line 7

Its supposed to copy the rows that have Car in column 1 to sheet 2

Thanks

Dave


Sub mycar()

x = 2

Do While Cells(x, 1) <> ""

If Cells(x, 1) = "Car" Then

Worksheets("Sheet1").Rows(x).Copy

Worksheets("Sheet2").Activate

eRow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

ActiveSheet.Paste Destination:=Worksheets("Sheet2").Rows(eRow)

End If

Worksheets("Sheet1").Active

x = x + 1

Loop

End Sub


sample data:

Product Ref Worker

Car 67 Dave

Carpet 675 Merle

Seat 54645 Amber

Car 345 Wayne

CarSeat 9890 Oc

Mirror 34 Brian

Car 34 nelly

Seats 444 Sue

hubcap 4452 Rob

Car 333 Sean

 

[Luke M]

The object Sheet2 is not necessarily the same object as Worksheets("Sheet2"). The former is using the VB name, the latter is the XL name.

eRow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

In this line, the [code]Rows.Count

doesn't necessarily refer to Sheet2, and I think this is what's causing problems.

Taking the liberty of re-writing the code,

[pre]Sub mycar()
Dim eRow As Integer
x = 2
Application.ScreenUpdating = False
With Sheet2
Do While Cells(x, 1) <> ""
If UCase(Cells(x, 1).Value) = "CAR" Then
eRow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Worksheets("Sheet1&#34.Rows(x).Copy .Cells(eRow, "A&#34
End If
x = x + 1
Loop
End With
Application.ScreenUpdating = True
End Sub[/code][/pre]

 

[dnessim]

thanks Luke,

I am still getting the same "object required errro"

I used your version with my original test data.

Dave

 

[Luke M]

Are you starting the macro with worksheet Sheet1 selected? I just now noticed that you have several Cell objects called out without the parent worksheet referenced. This shouldn't be causing a problem, but it might if you have a Chart sheet active when the macro is run.

 

[dnessim]

Luke,

Yes I started with Sheet1 selected, is that correct. What else should I try?

were you able to get your version to work?

Thanks

Dave

 

[NARAYANK991]

Hi Dave ,


Is this the problem ? Change your code to :


eRow = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row


Narayan

 

[dnessim]

Thank you Narayan,

that did get rid of my error, now I have this updated code:

but I am getting a "object doesnt support this property or method" on this line

##Worksheets("Sheet1").Active##

Dave


Sub mycar()

x = 2

Do While Cells(x, 1) <> ""

If Cells(x, 1) = "Car" Then

Worksheets("Sheet1").Rows(x).Copy

Worksheets("Sheet2").Activate

erow = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

ActiveSheet.Paste Destination:=Worksheets("Sheet2").Rows(erow)

End If

Worksheets("Sheet1").Active

x = x + 1

Loop

End Sub

 

[Luke M]

Should be

Worksheets("Sheet1").Activate

 

[dnessim]

ahhh, dang typo..

Thanks !

Thanks to Luke

Thanks to Narayan,