Split multi-tab workbook into separate workbooks

**ahurst105** · 02-28-2013, 10:37 AM

Hello,

I have a macro that I LOVE that takes multiple workbooks (single tab) and combines them into one workbook w/ multiple tabs. I'm looking for the reverse of that. I very frequently get workbooks with multiple tabs that I want to separate, but the move/copy function gets a little monotonous after awhile. I'd ideally like a folder where I can add said multi-tab'd workbook and then open my macro'd excel file and poof! Multiple workbooks would cascade across my screen like a flowing river. Anyone know how that might work?

I'm using Excel 2010.

Thanks,
Aaron

**Norie** · 02-28-2013, 10:41 AM

Aaron

You could adapt this so you can select the workbook(s) to split out.

As it is it splits out the workbook the code is in, ie ThisWorkbook.

Sub SplitWBtoWS()
Dim wbNew As Workbook
Dim WS As Worksheet
Dim strFilename As String

    For Each WS In ThisWorkbook.Worksheets

        strFilename = WS.Name

        WS.Copy

        Set wbNew = ActiveWorkbook

        wbNew.SaveAs ThisWorkbook.Path & Application.PathSeparator & strFilename

        wbNew.Close SaveChanges:=False

    Next WS

End Sub

**ahurst105** · 02-28-2013, 04:49 PM

Norie, this wasn't QUITE what I was looking for as far as ease of use, but quite honestly, after testing it out I think it's actually a better result than what I envisioned. It split all of my tabs into the separate worksheets and stored them in the folder the worksheet originated in - it's perfect and exactly what I need it !

Thank you so much for the help!

**Norie** · 02-28-2013, 06:51 PM

Aaron

What exactly where you looking for?

That code I posted was just an example which could be adapted.

For example, code could be added to allow the user to select the workbook they want to split out.