Bob
Thanks for the reply.
Not quite sure how to tackle this?? Do I enter all this code onto the
original worksheet and make copies of it??? I'm not familiar with VBA

Thanks.

"Bob Phillips" wrote:

cODE?

Public Sub ProcessData()
Dim i As Long
Dim iLastRow As Long
Dim iNextRow As Long
Dim cell As Range
Dim Sh As Worksheet

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
Set Sh = Nothing
On Error Resume Next
Set Sh = Worksheets(.Cells(i, "B").Value)
On Error GoTo 0
If Sh Is Nothing Then
Worksheets.Add(after:=Worksheets(Worksheets.Count) ).Name =
..Cells(i, "B").Value
iNextRow = 1
Else
iNextRow = Worksheets(.Cells(i,
"B").Value).Cells(Rows.Count, "A").End(xlUp).Row + 1
End If
Worksheets(.Cells(i, "B").Value).Cells(iNextRow, "A").Value =
..Cells(i, "A").Value
Next i
End With

End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Louise" wrote in message
...
sorry, something I did miss off. I need the final worksheets to look
like:

Name of Branch

Joe Bloggs
Fred Smith
karen Jones etc......

THank you.

"Louise" wrote:

Hi all

I have a worksheet consisting of staff names in column A and the branch
they
work at in Column B. (There are approximately 400 branches, each with
various numbers of staff).

I need to create a separate worksheet for each branch containing their
members of staff.

I have started to do this by using an Advanced Filter and using the
relevant
Department name as the Criteria, however, as there are over 400 branches,
it
is going to take me hours. I thought it would be easier to use Import
External Data and then I could use a Parameter to show each Branch on
separate pages, however, when I get to the end of the Wizard, it gives me
an
Error message.

CAn anybody think of an easier way of doing this? I'm sure there is
one....

Thank you.

Louise