How to split Excel into different files based on a column?

[stan255]

Hi, I'm looking to split the file based on the "degree" / B column.
The result would be each file will have all the schools with the same "degree" column.

Example:
File 1 = Degree 1 with all schools with degree 1
File 2 = Degree 2 with all schools with degree 2

Here's a screenshot of the file.
excelsplit.png

Sample workbook:excelsplit.xlsx

Any idea how we can do this?

[nilem]

Hi stan255,
try this

Please Login or Register  to view this content.

[stan255]

Thanks nilem! It looks like its working.
How do you change where the files will be located? It would be pretty bad if there are 1,000 files on the desktop.

[nilem]

change this line

Please Login or Register  to view this content.

to something like

Please Login or Register  to view this content.

[stan255]

I just found out something, the VBA only splits the file up to degree 5.
In the file, there are up to degree 9 like school 2 have degree 1,2,3,4,5,6,7,8,9 and school 3 have degree 1,2,3,4,5,6,7

[nilem]

Oops, sorry
change this line (instead of x(i,1))

Please Login or Register  to view this content.

[stan255]

Excellent! Thanks so much for the help, nilem! +rep

Edit: I got 1 last question, how do you make the split files to be in CSV format? Right now, all the split files are in xlsx.
My import software has problems importing xlsx files

[nilem]

try to change this part

Please Login or Register  to view this content.