Filtering data from one work sheet to another

Hi,

Hope you guys will be able to help me.

I have this excel sheet with 1000's of rows of data and with 15 columns.

Data is in the following format

1001 - abc -123 - art - so on
1001 - def -345 - sdf - so on
1002 - ghi - sdfs - sad - so on
1003 - abc - dfd -dfs -so on
1003 - def - asd -ere -so on

What I want to do is ideally excel pulling out automatically all the 1001s to sheet 2 all 1002 to sheet 3, all 1003s to sheet 4 and so on.

The problem is the as in the example the number of rows under each category is different meaning like here there are two 1001s but only one 1002 and there could be ten 1005s. However the category is unique, meaning I would want all 1001s in a separate sheet.

If this can't be automated then I would be happy with a less than ideal situation where I have to go an enter/type in each worksheet say the category name and then excel brings up all the data under that category into that sheet. For example, in the above example I go to worksheet 2 and type in 1001 and the excell copies all the data from worksheet 1 of the category 1001 to the second sheet.

Hope I didn't confuse you guys and thanks in advance

@VBA Noob

What a quick reply.. Thanks ever so much. It worked like a dream... Really appreciate your help. Thanks again

Anish

Hi again,

Just to be difficult... is there any way to sort the new work sheets in ascending order to the right of the main sheet?

For example: Data Sheet, 1001, 1002, 1003, 1004 and so on?

At the moment it inserts the new worksheet to the left of the main sheet (or the sheet I am in ) in ascending order.

Sorry to be a pain


thanks