Splitting a file into multiple files as per a column

**DCS2010** · 09-02-2010, 07:41 AM

Hi,

is there anyway of making a macro that when run it splits the main workbook into several workbooks as per a column in the main workbook.

eg:

Column A has only 10 differing entries. Each of the 250 lines of the workbook has one of those 10 values in column A. I am trying to split the file into 10 seperate files for each one of those values. sorry, its quite hard to explain.

imagine my column A is: (+ there is data all the way up to column AD)

a
b
b
a
c
a
b

i want to create a macro that will split this into a file of all the a's, a file of all the b's and a file of all the c's

Now i could record a macro where i do this manually but was wondering if theres a quicker way?

Thank you in advance

**StephenR** · 09-02-2010, 08:03 AM

Like this? Assumes you have headers in row 1, and you need to change the path.

Please Login or Register  to view this content.

**snb** · 09-02-2010, 08:59 AM

Please Login or Register  to view this content.

**kalasea2001** · 07-15-2011, 05:01 PM

Originally Posted by StephenR

Like this? Assumes you have headers in row 1, and you need to change the path.

Please Login or Register  to view this content.

Great reply! Worked perfect -Thank you!

**kbustin** · 06-01-2012, 08:29 AM

Hi there- I have a similar problem as DCS2010 and I am not very good at VB. I got a macro to work called breakout but it split the data into multiple worksheets within the same workbook. I am looking for a macro that will split a worksheet into multiple workbooks or files based on a column. The column has about 50 customer names, so at each change in customer name, I would like a new workbook created and saved in the same folder as the original file. I would also like the name of the new workbook to be a combination of the header row for that column and the name in the column. Here is an example of the header row and data:

Example IF Header Row is: 2012 P5 Time for and first company name is Tim Hortons then the File name should be 2012 P5 Time for Tim Hortons.xls
Can anyone help with this one? Thanks!

**kbustin** · 06-01-2012, 10:22 AM

Hi- Sorry forgot to mention I use Excel 2010 - thanks!

**arlu1201** · 06-01-2012, 10:29 AM

Kbustin,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.