Here's one play which automates it using non-array formulas ..

A sample construct is available at:
http://www.savefile.com/files/9963202
Auto-Filter_Data_To_Resp_Sheet_Non_Array_Approach.xls

In sheet: WS1 (the "master")

Assume data in cols A to E, headers in A1:E1,
data in row2 down, with the key col = col D (categories)

List the categories across in K1:O1,
viz.: Driver, Mtce, Cleaner, Secy, Mgr

Put in K2: =IF($D2=K$1,ROW(),"")
Copy across to O2, fill down to say, O100
to cover the max expected extent of data

Click Insert Name Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+1,32)
Click OK

The above defines WSN as a name we can use to refer to the sheetname in
formulas. It will auto-extract the sheetname implicitly. Technique came from
a post by Harlan.

Then, in a new sheet named: Driver
With the same col headers pasted into A1:E1

Put in A2:
=IF(ISERROR(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1! $K$1:$O$1,0)),ROWS($A$1:A1
))),"",INDEX(WS1!A:A,MATCH(SMALL(OFFSET(WS1!$J:$J, ,MATCH(WSN,WS1!$K$1:$O$1,0
)),ROWS($A$1:A1)),OFFSET(WS1!$J:$J,,MATCH(WSN,WS1! $K$1:$O$1,0)),0)))

Copy A2 across to E2, fill down to say, E10
(copy down by the smallest possible range sufficient to cover the max
expected extent for any category. Here, I've assumed that 9 rows (rows 2 to
10) is sufficient)

Cols A to E will return only the lines for cat: Driver from "WS1",
with all lines neatly bunched at the top

Now, we just need to make a copy of the sheet: Driver,
rename it as: Secy
and we'd get the results for cat: Secy

Repeat the copy rename sheet process
to get the rest of the category sheets (a one-time job)

Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"anny" wrote in message
...
Greetings (I'm sure this is easy, but I'm new to quite Excel)

I have a workbook with several sheets. WS1 has columns EmplNum, LName,
FName, Category, DateOfBirth, etc...
The Categories are driver, maintenance, cleaner, secretary, manager ....

I'd like WS2, 3, 4 etc to be automatically populated from WS1 by
category.
Lets say WS2 is for drivers. It needs to show the EmplNum, LName, FName
of the drivers in the first three columns of WS2. Other columns will hold
info specific to drivers. When a new driver is added to WS1, say,
he/she
needs to be updated on WS2. I can complete the missing entries in WS2 at
a
later time.

WS3 would handle the cleaners, and so on.

Much thanks for any help offered
anny