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.
Table of Contents
Create a New Workbook for Each Sheet VBA Code
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
- Step 1 declares a object variable. The ws variable creates a memory container for each worksheet the macro loops through.
- In Step 2, we use Application.ScreenUpdating = False to turn screen updating off to speed up this macro code.
- 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.
- In Step 4, we use Copy method to copy the worksheet to a new workbook.
- 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.
- Step 6 loops back to get the next sheet.
- Finally, you should turn screen updating on use Application.ScreenUpdating = True.
How to Use This Macro
To use this macro, you can copy and paste it into a standard module:
- Activate the Visual Basic Editor by pressing ALT+F11.
- Right-click the project/workbook name in the Project window.
- Choose Insert -> Module.
- Type or paste the code in the newly created module.