How to split worksheets into separate Excel workbooks

This VBA Code will easily split the worksheets into separate workbooks and save them in the same location. This code can use it for Excel for mac & windows.

Copy the below VBA Code by clicking on the copy box
Open Microsoft Visual Basic for Applications window Developer Ribbon Tab > Visual Basic (Shortcut keys as follows for mac & windows)
1. mac users fn + ⌥ [option] + F11
2. windows users Alt + F11
From the menu bar select Insert > Module and insert new Modules
Paste the code into the module.

Note: It is always good to take a back-up copy of your original before running the code on the original file.

Sub SplitWorkSheet()

    'Made It Easy by ExcelExciting.com | Faraz Shaikh | Updated : 20200801
    '******************************************************
    'Validation Check active workbook file path missing
    If ActiveWorkbook.Path = vbNullString Then
        MsgBox "Please save the workbook to run the code", vbCritical, "Excel Exciting | Faraz Shaikh"
        Exit Sub
    End If
    '******************************************************
    Dim FilePath As String
    FilePath = Application.ActiveWorkbook.Path 'Extracting the current workbook File Path
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    '******************************************************
    'Check the OS to add the path separator accordingly
        CheckOS = Application.OperatingSystem
        If CheckOS Like "*Mac*" Then
            OS_Separator = "/"  'for Mac
        Else
            OS_Separator = "\"  'for Windows
        End If
    '******************************************************
  
    'Loop Starting here
    For Each ws In ThisWorkbook.Sheets
    
        ws.Copy
        Application.ActiveWorkbook.SaveAs FileName:=FilePath & OS_Separator & ws.Name & ".xlsx"
        Application.ActiveWorkbook.Close False
    
    Next
  
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
  
End Sub

Was the information helpful in this blog?