splitting data into multiple tabs based on column criteria

I have testing data that I commonly need to separate out into different teachers and schools. I want to separate the file into separate sheets using criteria from a given column, and then move each sheet to a new book. (I often have to do some variation of this with a variety of files... I imagine this is a common task.) I've found the following code that does most of what I want, except for a couple things as mentioned below.

Please Login or Register  to view this content.

Goal #1 - it only copies one row of my header, but my header usually takes 6 or 7 rows, all of which should be copied as is into each new sheet.
Goal #2 - I'd like it to also move each sheet to a new book

Any assistance on accomplishing these goals would be greatly appreciated. Samples are attached and linked below. Thanks!

Attachment 341533 (simple workbook)
Attachment 341534 (macro-enabled with above code)

https://drive.google.com/file/d/0B0N...it?usp=sharing (macro-enabled with the above code)
https://drive.google.com/file/d/0B0N...it?usp=sharing (simple workbook)

Hi bardobhb,

Clear the note in cell H34 and then try this:

Please Login or Register  to view this content.

HTH

Robert

Hi Brent,

I changed it to save as an xlsx, because otherwise I lost essential formatting when I opened the csv

I have also incorporated this on the attached workbook.

when it copies the sheet into a new workbook it does still change some non-essential formatting - it changes the headers to yellow and removes the red cells (which were there to highlight wrong answers). I can live happily without this formatting, but it would be nice to keep. Do you know of anyway to prevent it from making these changes?

For some reason the color palette wasn't reconsigning the cells interior color. I have tried to replicate the color via the Fill Color icon and all seems good (I may have inadvertently missed a cell here or there).

Is it possible to get it to work out of my Personal.xlsb? Maybe if i change the instances of ThisWorkbook to active work book, or something of that sort? It's no big deal to past it into whatever file I'm separating, but perhaps it doesn't hurt to ask.

You could try but you'd have to make it point to the relevant workbook. I'd just leave it on whatever workbook you want.

Regards,

Robert

Worked great, thanks so much! It still changed the formatting when moving to a new file

Strange it didn't for me

I'm glad we were able to help in any case