This macro might get you what you want ...
Assuming that your data is in a workbook named DataBook.xls in a
table named "AllData",
and that you have a cell named "AgentID" where you can enter the
relevant AgentID,
and that column 1 in the data table holds the AgentID's ...

Sub FilterAndCopy()
Application.Goto Reference:="AllData"
Selection.AutoFilter Field:=1, Criteria1:=Range("AgentID")
Selection.Copy
Workbooks.Add Template:="Workbook"
Range("A1").Select
ActiveSheet.Paste
Windows("DataBook.xls").Activate
Application.CutCopyMode = False
Selection.AutoFilter
Application.Goto Reference:="AgentID"
End Sub

It may need a few tweaks but that should get the ball rolling.
Rgds,
ScottO

"ckjaime"
wrote in message
...
|
| I have been searching for a while now and have not been able to
find a
| solution to my problem. I have an excel document that contains
many
| customer records as well as the agent that assisted that customer.
I
| need a search utility that will search the excel spreadsheet for an
| agent's name, and then copy the entire row (not just the matching
cell)
| of data, including the information about the customer to a new
worksheet
| or workbook (it doesn't matter). Basically, I am providing this
service
| to my agents so that when they need a summary of the accounts that
they
| have serviced, I can do a search and all of the data regarding
every
| record in which they were the agent can be provided.
|
| I did find a utility called PowerGREP, however it seems that the
| utility is geared more towards searching through more than one
excel
| document for matching terms and is a little more complex than I was
| hoping for. If anyone can point me in the right direction, or if
| further explanation is required please let me know. Thanks for
| everyone's time.
|
| James
|
|
| --
| ckjaime
| -------------------------------------------------------------------
-----
| ckjaime's Profile:
http://www.excelforum.com/member.php...o&userid=25249
| View this thread:
http://www.excelforum.com/showthread...hreadid=387402
|