Find All and then Copy the Data...

[Mr B]

I have a sheet setup that is a list of people who have signed things out from our office. It includes date/timestamp information, user IDs, and items borrowed

I want a way to put someone's ID in a box and have it then do a search and if it finds that ID in the list, it would Copy the whole row (just to make it easier) onto a new Sheet. This I can do

However, I then want it to continue through the sheet looking for more instances of the text and to continue to copy the data for all rows where it finds the person's ID. Basically to pull a history for the user and see what they have checked out and when

How would I do the command (or loop) to repeat the search until it hits the end of the data

Can I somehow put a set VAR = cells.find... inside of a loop and say Search, copy data, search again, until it doesn't find it

Any guidance would be great

 

[Ron de Bruin]

Try this example
http://www.rondebruin.nl/copy5.htm

Post back if you need more help


--
Regards Ron de Bruin
http://www.rondebruin.nl

I have a sheet setup that is a list of people who have signed things out from our office. It includes date/timestamp information, user IDs, and items borrowed.

I want a way to put someone's ID in a box and have it then do a search and if it finds that ID in the list, it would Copy the

whole row (just to make it easier) onto a new Sheet. This I can do.

However, I then want it to continue through the sheet looking for more instances of the text and to continue to copy the data for

all rows where it finds the person's ID. Basically to pull a history for the user and see what they have checked out and when.

 

[Ron de Bruin]

Hi Mr B

With WS.Range("YourRange")
'Use a Dynamic range name, http://www.contextures.com/xlNames01.html#Dynamic
'This example filter on the first column in the range (change the field if needed)

This is the range you filter

.AutoFilter Field:=1, Criteria1:=Str

It will filter on the first column for the text in the string.
Str = "Netherlands"
You filter in column A in this example

Set WSNew = Sheets.Add
.Cells.SpecialCells(xlCellTypeVisible).Copy WSNew.Range("A1")
End With

It will add a new sheet to the workbook and copy all the visible cells.
All the cells with Netherlands in column A to that sheet

WS.AutoFilterMode = False

Show all the data

On Error Resume Next
WSNew.Name = Str
If Err.Number > 0 Then
MsgBox "Change the name of : " & WSNew.Name & " manually"
Err.Clear
End If
On Error GoTo 0
End Sub

Give the sheet you add a name(name of the Str )