I have a list of employees and the names of the teams that they work in. I
would like to transfer all of the names of people in one division onto
another worksheet, repeating this so that each worksheet shows a different
team list.

I have headed each page with the team name as per the column in the original
list and I have tried to use the Lookup functions which have only populated
the fields with the first name on the list.

I know i could sort and then cut and paste, but ideally i would like a
worksheet function as this may be a regular occurance,