Solved: Using find and copy, but only want it to search in 1st column

[Nikolette]

Hello all,

I'm using the macro below to search for a user-defined number (via message box) in column A, and copy/paste only the rows that begin with that value into another sheet. The only problem is that it currently is looking in all of the columns for the user-defined number and copy/pasting the rows containing it, no matter which column the number appears in. How do I tell it to only grab the rows that have the user-defined number in column A?

[vba]Sub CopyPaste042013()
Dim Number As Integer
Number = Application.InputBox("Enter the number you are looking for below", "Enter your number", , , , , , 1)
Sheet2.Cells.Delete
Application.ScreenUpdating = False
Dim xRow&, NextRow&, LastRow& NextRow = 2
LastRow = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
For xRow = 1 To LastRow
If Application.CountIf(Rows(xRow), Number) > 0 Then
Rows(xRow).Copy Sheets("Sheet3").Rows(NextRow)
NextRow = NextRow + 1
End If
Next xRow
Application.ScreenUpdating = True
MsgBox "Macro is complete, " & NextRow - 2 & " rows containing" & vbCrLf & _
"''" & Number & "''" & " were copied to Sheet2.", 64, "Done"
End Sub [/vba]
Thanks so much,
Nikolette

[patel]

instead of cells.find use columns("A").find

[Nikolette]

Will try that, Patel - thank you...

[Nikolette]

Nope; still copying every row that has a 1 in any of its columns...

[Nikolette]

Turns out replacing what's there with this line works :-):

[VBA]If Application.CountIf(Range("A" & xRow), Number) > 0 Then[/VBA]

Thanks for your help!