Solved: Copy rows selectively

[realitybend]

I need some vba that will copy a row in "Sheet1" if it has a valid number in column B.

Any suggestions?

Thanks

[mdmackillop]

[VBA]Sub Test()
Dim cel As Range
With ActiveSheet
For Each cel In Intersect(.Columns(2), .UsedRange)
If IsNumeric(cel) Then
cel.EntireRow.Copy Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1)
End If
Next
End With
End Sub
[/VBA]

[realitybend]

Wow. Thanks.

I have a couple more things I need it to do:
It should only copy cells from row 3 or below.

Also, I need it to look at the numbers in column A for the row it's copying, and if there's an identical number in column A of Sheet2, it needs to overwrite it.

[mdmackillop]

Try
[vba]
Option Explicit
Sub Test()
Dim cel As Range, Fnd As Range, colA As Single
With ActiveSheet
For Each cel In Intersect(.Columns(2), .UsedRange)
If IsNumeric(cel) and cel.Row > 3 Then
colA = cel.Offset(, -1)
Set Fnd = Sheets(2).Columns(1).Find(colA, LookIn:=xlValues, lookat:=xlWhole)
If Not Fnd Is Nothing Then
cel.EntireRow.Copy Fnd
Else
cel.EntireRow.Copy Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1)
End If
End If
Next
End With
End Sub

[/vba]

[realitybend]

Awesome!

Thanks so much.

[realitybend]

Pardon my ignorance, but why is it "Offset(, -1)" that and not "Offset(0, -1)"?

[mdmackillop]

You can use default values in code, where the default here is 0

Similar to
Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
You can use
Find(What, , , xlwhole)
to reduce typing

[realitybend]

Thanks.