Save Each Excel Worksheet as a Separate Workbook Using VBA

If you have a workbook with several worksheets, and you want to separate and save each worksheet as its own workbook without going through the manual labour of Move or Copy and saving each new workbook manually, this hack has got you covered.

Whether you’re splitting regional sales reports for different managers or separating monthly data for archiving, this VBA macro acts as your personal assistant, executing the "grunt work" with flawless precision. You’re about to turn a twenty-minute chore into a three-second victory.

All you need to do is Insert a new module in your Visual Basic Editor (you can access this from the Developer tab or through the keyboard shortcut Alt+F11), and place the following lines of code in the new module.

This VBA code runs through all the worksheets in your workbook, copies each one into a new Excel document, and saves the new document with the sheet name of the worksheet in the folder you specify.

Sub ExportAndSaveSheet()

Dim newWb As Workbook
Dim sheetName As String

For Each s In ActiveWorkbook.Sheets
        
        sheetName = s.Name
        Set newWb = Workbooks.Add
        s.Copy before:=newWb.Sheets(1)
        Application.DisplayAlerts = False
        newWb.Sheets("Sheet1").Delete
        Application.DisplayAlerts = True

        newWb.SaveAs Filename:="C:\Users\wunmitee\Desktop\" & sheetName & ".xlsx"
        newWb.Close
    
Next s



End Sub

Remember to change the section in bold in the code above (Filename) to your own workstation file path.

Save your workbook in a macro-enabled format (.xlsm or .xlsb), and go ahead to run the macro magic using the keyboard shortcut Alt+F8 (or Developer>Code:Macros).