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)
- mac users fn + ⌥ [option] + F11
- 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 SubWas the information helpful in this blog?
- If yes, hit that share button and show the excitement to the world.
- Subscribe to our free posts.
Join 2,926 other subscribers
If no, please let us know what to improve.
Having trouble with any Office Apps. Feel free to ask and answer queries at our forums section.
superb code
thank you mate.
Welcome 😎
Thank you for providing this VBA code. I tried using it and it does create separate files, but they are not ‘Microsoft Excel Workbook’ types . They are ‘XLXS File’ Types I am running the VBA in Windows 10 with Microsoft 365 Apps for enterprise, version 2102 (build 13801.20960) and this is how I have it coded in my Module: Sub saveasseparateexcel() Dim FilePath As String FilePath = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False For Each ws In ThisWorkbook.Sheets ws.Copy Application.ActiveWorkbook.SaveAs Filename:=FilePath & “\” & ws.Name & “.xlxs” Application.ActiveWorkbook.Close False Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub… Read more »
Hi Faraz – Thanks for sharing this code! If we wanted to save the exported worksheets as csv files, how would you modify the code?
I am writing the VBA script on a Mac and did not have success adding “, FileFormat:= xlCSV” to the code on line 30