create multiple workbooks from a single workbook

**maacmaac** · 06-03-2009, 11:22 PM

On a weekly basis, I receive a single worksheet in a workbook that contains ~30,000 rows of product sales.

Row one contains column headings that is unique to all other rows. Column A contains the store number that sold the product. There are ~50 unique store numbers.

I am trying to create a macro that will break the report up into seperate workbooks.

For example, assume in column A there are 30 rows of data for 3 different store numbers (say store 112, 386, & 798, each with 10 rows of data). I want to create 3 new workbooks and include the same column heading for each. For example, name new workbooks as follows: "Store 112", "Store 386", "Store 798".

I am hoping this is possible. Thank you in advance for any commnets.

**Justin Young** · 06-04-2009, 02:43 AM

See the attached - I expect that its very close to what you are looking for.

**JBeaucaire** · 06-04-2009, 03:30 AM

This was fun to write. It will create a temp sheet, create a list of store numbers, then make and save of workbooks for each store in the default directory. Give it a whirl. When you're done, look in your directory for all the workbooks.

Please Login or Register  to view this content.

**maacmaac** · 06-04-2009, 09:04 AM

The code is awesome but how can I preserve the formatting of sheet 1 to all new sheets created (i.e., I want column width, alignment, cell formatting, page setup, etc. to be the same)?

Thanks

BTW, I ended up using JBeaucaire's code but both solutions work.

**JBeaucaire** · 06-04-2009, 12:31 PM

Hm...I guess that's doable. I would need to see a sample sheet to test on, one with at least a store or two sample data and all your desired formatting.

**maacmaac** · 06-04-2009, 01:16 PM

JB,

Attached two files

First file is sample data with code.
Second file "Store 118" is desired output

Basically, the desired output sheet is formatted in the same exact manner as the sample data (same column width, page setup w/ page numbers, cell formatting, etc).

Thanks again for all your comments.

**JBeaucaire** · 06-04-2009, 01:50 PM

Try this amended code, it appears column widths require a completely separate procedure. Changes highlighted in red:

Please Login or Register  to view this content.

**maacmaac** · 06-05-2009, 11:18 AM

That did the trick. Thanks for quick response.

**hweeliangteo** · 05-07-2013, 02:00 AM

Excel noob here, I opened the examples but couldn't get it to work. Using Excel 2010, what did I do wrong?

**JBeaucaire** · 05-07-2013, 02:36 AM

No way for us to know that.

Here's the final published macro for taking the data from a single sheet and creating separate workbooks based on the values in a specified column.

===========
'SHEET1 TO MANY WORKBOOKS
Here's a macro for parsing rows of data from one sheet to many workbooks based on one column, workbooks named for the same values in that column.

Sheet1 to Many Workbooks

My macro names the workbooks for values in the column PLUS today's date, you can take a stab at removing the date part...or leave it in, it's a good technique.