Splitting Data Into Separate Files Based on Pre-Populated List

[sigma1113]

Hello, Every week our company gets a planning schedule that has a list of part numbers in column A and other information such as quantity and ship date in the other corresponding columns.

These parts are handled by different people in the company so what I want to do is split up the large file into smaller files that only have each persons parts.

I have read other forum posts that show macros on how to do this but it is actually scanning the file for a persons name and then creating a file based on the name, in my file there is no names only part numbers.

I believe it would work something like this; we would first need to populate a list with all possible part numbers and their corresponding employee. Then each week when the planning sheet comes in it would scan that list and dump the corresponding parts into a file based on the persons name. I just don't know anything about VBA or the nomenclature it uses.

Any help on a code for this would be greatly appreciated.

Thanks

[JBeaucaire]

I would have a secondary sheet (Sheet2) that lists the Part Numbers (column A) and the Employee that handles that part (Column B).

Then when the data sheet comes in, you add a column to the beginning of the data (insert column A) and put in a VLOOKUP() formula to lookup the name for each part. In A2, then copied down:

=VLOOKUP($B2, Sheet2!$A:$B, 2, 0)

Adding that column/Formula takes 10 seconds.

Then a parsing macro can then split that data out onto separate sheets based on the names in column A.

'SHEET1 TO MANY SHEETS
Here's a macro for parsing rows of data from one sheet to many sheets named for the same values in a specific column.

Sheet1 to Many Sheets

It not only can parse the rows, it can create the sheets if they are missing. There is a sample sheet there where you can test this out.

[sigma1113]

Thanks, The =VLOOKUP helped a lot, I tried to get the list to split into tabs but didn't work. I didn't spend too much time on it but ill go back and give it another shot.

[JBeaucaire]

If you want to post your file with your edited version of the macro in it, I can see what you might have done wrong implementing it.

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.

[sigma1113]

Dummy schedule.xls

Dummy Name list.xls

Dummy Schedule Problems.doc

The two excel files are the dummy files stripped down to the essentials

The word document are screen shots of a rough step by step process and the error i am getting with the ParseItems macro

[JBeaucaire]

Wow, I'm completely stumped. Your worksheet has something "illegal" in it and I have no idea what it is. Whatever it is stop the AdvancedFilter from working, period.

Anyway, I've removed the whole section and used another method to collect all the unique values from the vCol. Now that I've seen that odd behavior, I will probably change my published macro.

Please Login or Register  to view this content.

[sigma1113]

Awesome! works perfect on my dummy file (along with the code for Vlookup and your code to add rows) i just need to figure out how to put everything all into one macro.

Thanks so much

[JBeaucaire]

Do you need to? If you have three separate sequentially working macros, create a 4th to run them all in order:

Please Login or Register  to view this content.