Extract columns and create separate sheet based on column criteria

[Frank Bacchus]

Hi,
I have a spreadsheet with the following data:

Order Number In production Cutting Machinist Casualty Analyst Status Date
A100100 01/09/2015 03/09/2015 05/09/2015 John M Cutting 02/09/2015
A100100 01/09/2015 02/09/2015 Mary T Casualty 05/09/2015
A100200 01/09/2015 05/09/2015 Jack S Machinist 02/09/2015
A100200 01/09/2015 02/09/2015 05/09/2015 Mary T In Production 01/09/2015
A100200 01/09/2015 John M Query 09/09/2015
A100300 01/09/2015 02/09/2015 03/09/2015 Frank B In production 01/09/2015
A100300 01/09/2015 02/09/2015 02/09/2015 Jack S

I would like to create separate sheets for each individual based on their name (Analyst) and with some of the columns included in their sheet. So, my first result would be:

Order Number In production Cutting Analyst Status Date
A100100 01/09/2015 03/09/2015 John M Cutting 02/09/2015
A100200 01/09/2015 John M Query 09/09/2015

My Second sheet with be like this:

Order Number In production Cutting Analyst Status Date
A100100 01/09/2015 02/09/2015 Mary T Casualty 05/09/2015
A100200 01/09/2015 02/09/2015 Mary T In Production 01/09/2015

and so on...(see attachment)

 

[Luke M]

Hi Frank,

I'd suggest starting here:
http://www.rondebruin.nl/win/s3/win006_4.htm

Ron has quite the library of code for merging/copying data, and he does a ood job providing comments in the code if you need to change things.

 

[Chihiro]

See attached method using PivotTable.

Each sheet you just copy and paste the one created (so as not to increase number of duplicate pivot cache), and only format and filter adjusted.

 

[Frank Bacchus]

Hi Frank,

I'd suggest starting here:
http://www.rondebruin.nl/win/s3/win006_4.htm

Ron has quite the library of code for merging/copying data, and he does a ood job providing comments in the code if you need to change things.

Hi Luke:

Thanks for the information and it was very helpful. I was looking at the Ron's macro created for “Create a new sheet for all Unique values” and was getting some messages when I triggered it from a separate sheet. I have buttons to invoke the assigned macro, which I created in the “data” sheet. This is the message I am getting: "Run time error "1004" Method Range of object _Global failed.

When I run the macro from the “Data” sheet, it runs fine but when the “data” sheet is not the active sheet, it errors out. How can I fix this? I have attached the spreadsheet. Thanks in advance for your support.

frank

 

[Chihiro]

Change this portion.

Set My_Range = Range("A1:D" & LastRow(ActiveSheet))

And

If ActiveWorkbook.ProtectStructure = True Or _

To

Set My_Range = ThisWorkbook.Worksheets("Data").Range("A1:D" & LastRow(ActiveSheet))

And

If ThisWorkbook.ProtectStructure = True Or _

 

[Frank Bacchus]

Thanks alot. Last item, If I waned to only select specific columns to be placed in the new sheet, in this case the "RecordsOfTheNetherlands" sheet, how can I do that? So I want the "RecordsOfTheNetherlands" to contain Name and Birthday. I also noticed that the headings are not coming over with the rows. Is there a way for me to tell the macro to bring the headings over? Thanks.

New file attached.

 

[Chihiro]

I'd go with different approach in that case.

See attached.

 

[Chihiro]

Just realized there's duplicates in the data set. See attached with slight modification.