Thread: Macro to Split Workbook and Store New Workbooks in New Folders

Hi All,

This is my first thread! I am new to macros and have found one that splits workbooks but isn't specific to my needs. Here is what I need the macro to do:

I have a workbook with many tabs. I need a macro to split each tab off into a new workbook that will be saved using the name of the tab. I also need each new workbook to be saved in it's own folder that is named the same as the tab.

If anyone already has something like this and is willing to share that'd be a true time saver.

Thanks a ton in advance!

try this code:

Option Explicit
Sub test()
Dim ipath As String, sh, newpath As String
Application.ScreenUpdating = False
ipath = ActiveWorkbook.Path & "\"
On Error GoTo handler
For Each sh In ActiveWorkbook.Sheets
    newpath = ipath & sh.Name
    MkDir (newpath): sh.Copy
    With ActiveWorkbook
        .SaveAs Filename:=newpath & sh.Name & ".xls"
        .Close
    End With
Next
On Error GoTo 0: Application.ScreenUpdating = True
Exit Sub
handler:
MsgBox "Folder with the same name already exists!" & Chr(10) & "Please note, some folders may have been created", vbExclamation
Application.ScreenUpdating = True: Exit Sub
End Sub

Hey watersev,

Thanks for the quick reply. This is working for the most part but there are two issues:

1. The file names and folder names are the name of the sheet in the workbook x2.
Ex. Sheet Name = Step01 ------> Workbook/Folder Name = Step01Step01

2. The files are not inserting into the folders. It creates the files and the folders but does not put the file with the same name into the proper folder with the same name.