Macro to Split Workbook into separate Workbooks

**jmurray** · 07-07-2009, 07:26 AM

Hi

I have a large workbook with many worksheets which are all grouped into pairs - i.e. Sheet1 & Sheet2 go together, Sheet3 & Sheet4 go together; Sheet5 & Sheet6 go together, etc. etc.

I need a macro to divide this workbook into separate workbooks where each group of worksheets has a separate file of its own and I want to name the new workbooks after the second sheet in each group:

i.e. A new workbook for Sheet1 & Sheet2 called Sheet2.xls; a new workbook for Sheet3 & Sheet4 called Sheet4.xls; a new workbook for Sheet5 & Sheet6 called Sheet6.xls; etc. etc. etc.

Appreciate any help I can get on this.
Ta
John

**JBeaucaire** · 07-07-2009, 07:46 AM

The two sheets are in order and together?
There are an even number of sheets?
The original workbook is to be preserved?

**JBeaucaire** · 07-07-2009, 08:04 AM

Give this a try:

Please Login or Register  to view this content.

Be sure to set your strPath properly, including that closing "\".

**jmurray** · 07-13-2009, 04:58 AM

Hi

Thanks for reply. Unfortunately, I get a message saying that "Run-time error 9: Subscript out of Range" - and when i click debug it takes me to the line below:

Sheets(Array("Sheet" & i, "Sheet" & i + 1)).Copy

Any thoughts?

Thanks
John

**royUK** · 07-13-2009, 05:23 AM

Can you attach a dummy workbook.

**jmurray** · 07-13-2009, 05:53 AM

Hi Roy

Have attached dummy workbook as requested - appreciate your help.

Ta
John

**royUK** · 07-13-2009, 06:29 AM

The suggestd code doesn't wok because the array needs to contain the sheet names, yours ar not named Sheet1, etc. This amendment will get the sheet names & build the array

Please Login or Register  to view this content.

**jmurray** · 07-13-2009, 06:53 AM

Thank you - works perfectly!

**JBeaucaire** · 07-13-2009, 09:12 AM

Please Login or Register  to view this content.

That's great, Roy!

**royUK** · 07-13-2009, 11:45 AM

Thanks both

jmurray please mark this thread solved

**chenelas** · 07-14-2009, 03:55 AM

Hi to all,

I want the same thing for my file--split workbook into separate sheets. but with some additional "features".
What i want to achieve is to divide, thru macro/VB, the entries in my main file into several files, say by 50.
I attached files for reference. Whereas, the Dummy.xls is the main file and Book1 & Book2 are the desired output.

Have a nice day

**royUK** · 07-14-2009, 04:00 AM

chenelas
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.

**chenelas** · 07-14-2009, 04:44 AM

oppss!
im so sorry.

Macro to Split Workbook into separate Workbooks

LinkBack

Thread Tools

Rate This Thread

Display

Macro to Split Workbook into separate Workbooks

Re: Macro to Split Workbook into separate Workbooks

Re: Macro to Split Workbook into separate Workbooks

Re: Macro to Split Workbook into separate Workbooks

Re: Macro to Split Workbook into separate Workbooks

Re: Macro to Split Workbook into separate Workbooks

Re: Macro to Split Workbook into separate Workbooks

Re: Macro to Split Workbook into separate Workbooks

Re: Macro to Split Workbook into separate Workbooks

Re: Macro to Split Workbook into separate Workbooks

Re: Macro to Split Workbook into separate Workbooks

Re: Macro to Split Workbook into separate Workbooks

Re: Macro to Split Workbook into separate Workbooks

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions