Find All and then Copy the Data...
 

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

Find All and then Copy the Data...
 

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

Post back if you need more help


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


"Mr B" wrote in message ...
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.

Find All and then Copy the Data...
 

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
For example WS.Range("A1:G100")
<Look at the link for using a Dynamic range name


.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 )



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


"Mr B" wrote in message ...
I'm assuming this is the code part that does most of the work. I'm not familiar with these commands so could you break it down
for me?

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)
.AutoFilter Field:=1, Criteria1:=Str
Set WSNew = Sheets.Add
.Cells.SpecialCells(xlCellTypeVisible).Copy WSNew.Range("A1")
End With



Also what does the rest of this do?

WS.AutoFilterMode = False
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