I have a worksheet that contains a team of people's names of in column
A that I would like to find in another worksheet lets say an employee
timesheet worksheet.
Let's say the first workhsheet is called "TEAM A" and contains the
names Amy, Ben, Carl, David, Earl.
The other worksheet "Employee Timesheet" contains the entire list of
employees that were working on a given day which may or may not include
the people in "TEAM A" worksheet.

I want to write a macro that would allow a user to do search for all
the values in "TEAM A" that appear in appear in the "Employee
Timesheet" worksheet and then take the row with the employees name
(which would also include the time worked) and add that to a range that
I can paste into a user specified destination.

I need to know how to do the search in the "Employee Timesheet"
worksheet based on the people in "Team A" worksheet.

Here is a start. Tailor to fit.

Sub MatchAndCopy()
Dim rng as Range, rngA as Range
Dim cell as Range, rngMatch as Range
Dim sh as Worksheet, i as Long
With Worksheets("Employee")
set rng = .Range(.Cells(2,1),.Cells(2,1).End(xldown))
End With
With worksheets("TEAM A")
set rngA = .Range(.Cells(2,1),.Cells(2,1).End(xldown))
End With
i = 2
set sh=Worksheets("Matches")
for each cell in rngA
res = Application.Match(cell.Value,rng,0)
if not iserror(res) then
set rngMatch = rng(res)
sh.Cells(i,1) = rngMatch
sh.Cells(i,2) = rng.offset(0,1).Value
i = i + 1
end if
Next
End Sub

--
Regards,
Tom Ogilvy




wrote in message
oups.com...
I have a worksheet that contains a team of people's names of in column
A that I would like to find in another worksheet lets say an employee
timesheet worksheet.
Let's say the first workhsheet is called "TEAM A" and contains the
names Amy, Ben, Carl, David, Earl.
The other worksheet "Employee Timesheet" contains the entire list of
employees that were working on a given day which may or may not include
the people in "TEAM A" worksheet.

I want to write a macro that would allow a user to do search for all
the values in "TEAM A" that appear in appear in the "Employee
Timesheet" worksheet and then take the row with the employees name
(which would also include the time worked) and add that to a range that
I can paste into a user specified destination.

I need to know how to do the search in the "Employee Timesheet"
worksheet based on the people in "Team A" worksheet.