Skip to main content

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
'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.

Most VBA code should be placed in Standard Modules unless specified.

If you see a comment '------------------ Modules------------------ in the code header that means put the code in a Standard Module. For more information, learn this course: Where should I put the Excel VBA code?

The following steps teach you how to put VBA code into a Standard Module:

  1. Activate the Visual Basic Editor by pressing ALT + F11.
  2. Right-click the project/workbook name in the Project Window.
  3. Choose Insert -> Module.
  4. Type or paste the code in the newly created module. You will probably need to change the sheet name, the range address, and the save location.
  5. Click Run button on the Visual Basic Editor toolbar.
  6. For more information, learn this course: Programming with Excel VBA

Leave a comment

Your email address will not be published. Required fields are marked *

Format your code: <pre><code class="language-vba">place your code here</code></pre>