Export Sheets as Individual Excel Workbooks, in Minutes!

So you have a workbook with several worksheets, maybe up to 30. And you need each worksheet exported one after the other, so that you now have 30 Excel workbooks instead of one. Is there a way to get this done in less than one minute - which takes out the hard work of right-clicking every single tab, selecting "Move or Copy," and manually saving each one while your coffee goes cold. That’s not an adventure; that’s a chore. And on this blog, we don’t do chores - we build engines.

Follow these steps to turn your one workbook into a fleet of individual files in seconds.

Step 1: Open the VBA Editor

First, we need to go behind the scenes. Open your Excel workbook and press ALT + F11. This shortcut transports you into the Visual Basic Editor, the engine room where the real power of Excel is stored.

Step 2: Insert a New Module

Once inside the editor, look at the top menu. Click Insert and then select Module. A blank white window will appear - this is your canvas. This is where we’ll drop the "treasure map" that tells Excel exactly how to split your files.

Step 3: Paste the Code

Copy and paste the following code into that blank module window. This script tells Excel to loop through every sheet, copy it to a new book, and save it in the same folder as your master file.

VBA

Sub SplitSheetsIntoWorkbooks()
    Dim ws As Worksheet
    Dim DisplayStr As String
    Dim Path As String

    Path = ThisWorkbook.Path & "\"

    For Each ws In ThisWorkbook.Worksheets
        ws.Copy
        ActiveWorkbook.SaveAs Filename:=Path & ws.Name & ".xlsx"
        ActiveWorkbook.Close SaveChanges:=False
    Next ws

    MsgBox "Export Complete! All sheets have been exported.", vbInformation
End Sub

💡 Pro Tip: Ensure your "Master" workbook is saved before you run this. The code uses ThisWorkbook.Path to decide where to save the new files. If your master isn't saved yet, Excel won't know where to put the babies!

Step 4: Unleash the Automation

Close the VBA window (or just click back into your spreadsheet). Press ALT + F8, select SplitSheetsIntoWorkbooks from the list, and hit Run.

Watch your screen - Excel will flicker for a moment as it creates, saves, and closes each sheet at lightning speed. When the message box pops up saying "Expedition Complete!", check your folder. You’ll see every sheet sitting there as its own beautiful, independent Excel file.

PS: By the way, if you are curious about the Balanced Scorecard Template I used as my sample workbook for this Hack, check it out at https://exinfm.com/free_spreadsheets.html