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

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

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

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

No. Put the code in a standard code module and run it.

To add the code.
Go into the VB IDE, Alt-F11.
Open a new module, menu InsertModule
Paste the code into there.

Go back to Excel and run it.
Go to Excel. Alt-F11
Menu ToolsMacroMacros..., select the macro from the list
Click Run.

--
HTH

Bob

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

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