MS Excel : Split multiple sheets of One Workbook into Separate Workbooks

In continuation to my previous post on Split data into different sheets based on a column , we will now see on how to split the Workbook with multiple sheets into separate workbooks with sheet names as workbook names.

One we have completed the data split as per my previous post, we are left with a workbook with multiple sheets , each sheet will have data related to a USER and the sheet names are the respective USER_ID's.

Now save the workbook with some name and open the macro editor.

1. Press ALT+F11 , to open macro editor
2. Under Insert , Click on Module
3. Please paste the below code in the module 
4. Press F5 to run

Sub Splitbook()

Dim xPath As String

xPath = Application.ActiveWorkbook.Path

Application.ScreenUpdating = False

Application.DisplayAlerts = False

For Each xWs In ThisWorkbook.Sheets

    xWs.Copy

    Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xls"

    Application.ActiveWorkbook.Close False

Next

Application.DisplayAlerts = True

Application.ScreenUpdating = True

End Sub

This code will split the sheets of one workbook into multiple workbooks with sheet name as workbook name in the same folder of the original workbook.