Finding certain values and display them in another sheet

[Navvy]

Hello.

I am trying to find a method whereby I can display certain information on one sheet from an entire list on another sheet.

So, if I have the following list on "Sheet2"
John 1234 abcd
Tim 4345 jhgd
John 8989 jwek
John 4444 ouuu
etc, etc

The list on "Sheet2" is fairly large, so it would be good to use code. I would like to, say, select all those that match "John" and display them on sheet 1.

Thank You.
Ben

 

[xlbo]

Try somethig like this:

Dim mName As String, lRow As Long

mName = inputbox("Enter text to search for&quot
lRow = Sheets("sheet2&quot.Range("A65536&quot.End(xlUp).Row
i=1
sheets("Sheet1&quot.cells.clearcontents
With Sheets("sheet2&quot.Range("A1:A" & lRow)
Set c = .Find(mName, LookIn:=xlValues, lookat:=xlpart)
If Not c Is Nothing Then
firstAddress = c.Address
Do
sheets("Sheet1&quot.range("A" & i).value = c.text
i = i + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
else
msgbox &quot;Entered text not found&quot;
End If
End With

NB - this assumes all data is in column A on sheet2 and that there are no headers on sheet1 or sheet2 Rgds
Geoff

Si hoc legere scis, nimis eruditionis habes

 

[Navvy]

This is great! Thank You. One further question, Geoff. How would I move the entire row, which is from columns &quot;A&quot; to &quot;L&quot;?

 

[Navvy]

Geoff - one more question, sorry! How would I add another variable, for example I would like it to transfer both &quot;John&quot; and &quot;Tim&quot; records across to sheet1?

 

[xlbo]

for copying the row, use the following code:

Dim mName As String, lRow As Long

mName = inputbox(&quot;Enter text to search for&quot
lRow = Sheets(&quot;sheet2&quot.Range(&quot;A65536&quot.End(xlUp).Row
i = sheets(&quot;Sheet1&quot.range(&quot;A65536&quot.end(xlup).row
sheets(&quot;Sheet1&quot.cells.clearcontents
With Sheets(&quot;sheet2&quot.Range(&quot;A1:A&quot; & lRow)
Set c = .Find(mName, LookIn:=xlValues, lookat:=xlpart)
If Not c Is Nothing Then
firstAddress = c.Address
Do
sheets(&quot;Sheet2&quot.range(c.address & &quot;:&quot; & c.offset(0,11).address).copy destination:=sheets(&quot;Sheet1&quot.range(&quot;A&quot; & i)'all one line
i = i + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
else
msgbox &quot;Entered text not found&quot;
End If
End With

to do multiple searches, the easiest way I can think of would be to exclude the
sheets(&quot;Sheet1&quot.cells.clearcontents
line and run the code twice - once for each name
Rgds
Geoff

Si hoc legere scis, nimis eruditionis habes