WorkSheet macro problem (Excel 2000)

[PCGeek]

I am having difficulty with an Excel macro. I have an XLS with about 500 rows. (Col A is a unique ID). I have to from time to time extract various rows (yes, based on unique ID) and copy them to another sheet. I may do this three or 10 times/day. The content is never the same. I am trying to write a macro to let me key in each ID (press enter) and then when done, press a button, close. Each ID's row would be copied to sheet 2 and can be handled accordingly. This doesn't seem like a complicated macro, but being new to VBA, I am having a devil of a time. I've gotten it to clear sheet 2 before I start, but am having trouble getting it to keep asking for IDs until I stop.

Thanks

[LegareColeman]

The code below assumes that the IDs are entered as strings in Column A on Sheet1. It also assumes that you want to prompt the user to enter each ID that is to be copied. It should give you a start.

<pre>Public Sub CopyRows()
Dim lLastRow As Long, I As Long, J As Long
Dim strID As String
Worksheets("Sheet2").Cells.ClearContents
lLastRow = Worksheets("Sheet1").Range("A65536").End(xlUp).Row - 1
J = 0
Do While True
strID = InputBox("Enter next ID")
If strID = "" Then Exit Do
For I = 0 To lLastRow
If Worksheets("Sheet1").Range("A1").Offset(I, 0).Value = strID Then Exit For
Next I
If I > lLastRow Then
MsgBox "ID not found."
Else
Worksheets("Sheet1").Range("A1").Offset(I, 0).EntireRow.Copy _
Destination:=Worksheets("Sheet2").Range("A1").Offs et(J, 0)
J = J + 1
End If
Loop
End Sub
</pre>

[PCGeek]

Thank you Legare. Yes, I should have indicated they were strings. I'll change it up and let you know tomorrow. Thanks again
<img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>