Excel Macro: Create a New Workbook for Each Sheet

Sometimes you may need to create a new workbook for each sheet, it seems very boring to do it manually, in this situations, you can use the following macro.

Create a New Workbook for Each Sheet

'------------------ Modules ------------------
Sub SheetsToWorkbooks()
'Step 1:  Declare all the variables.
    Dim ws As Worksheet
'Step 2: Turn screen updating off to speed up your macro code
    Application.ScreenUpdating = False
'Step 3:  Start the looping through sheets
    For Each ws In ThisWorkbook.Worksheets
'Step 4:  Copy the target sheet to the new workbook
        ws.Copy
'Step 5:  Save the new workbook with sheet name.
        ActiveWorkbook.SaveAs ThisWorkbook.path & "\" & ws.Name
        ActiveWorkbook.Close SaveChanges:=True
'Step 6:  Loop back around to the next worksheet
    Next ws
'Step 7: Turn screen updating on
    Application.ScreenUpdating = True
End Sub

This macro will save each worksheet as a new workbook with the sheet name to the folder that source workbook is in. The new workbooks are closed after they are saved.

How This Macro Works

1. Step 1 declares a object variable. The ws variable creates a memory container for each worksheet the macro loops through.
2. In Step 2, we use Application.ScreenUpdating = False to turn screen updating off to speed up this macro code.
3. In Step 3, the macro starts looping through the sheets. The use of the ThisWorkbook object ensures that the active sheet that is being copied is from the workbook the code is in, not the new workbook that is created.
4. In Step 4, we use Copy method to copy the worksheet to a new workbook.
5. In Step 5, We save this new workbook in the same directory (ThisWorkbook.Path) as your original workbook, with the same filename (ws.Name) as the copied sheet.
6. Step 6 loops back to get the next sheet.
7. Finally, you should turn screen updating on use Application.ScreenUpdating = True.

How to Use This Macro