VBA Script for Splitting Excel Workbook
Recently I got an Excel file which contained more than 50 worksheets and I needed to split them into individual files.
After a lot of searching, I found this simple VBA script to split Excel files into individual worksheet.
Sub Splitbook()
Dim xPath As String
xPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In ThisWorkbook.Sheets
vis = xWs.Visible
xWs.Visible = xlSheetVisible
xWs.Copy
Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xlsx"
Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
In Excel, perform following functions:
- Go to Visual Basic editor. Developer > Visual Basic (Alt + F11)
- In VB editor, go to Insert > Module
- Now copy and paste the above script in the Module screen
- Now run the VBA code by pressing F5
- All worksheets (including the hidden sheets), will now be saved as individual files in the same directory as that of the original file.
Note - For some reason, Ghost doesn't allow adding some GIF files to the posts. Please refer this link to the original article to see above steps in action: https://auditmonk.wordpress.com/2018/04/22/vba-script-for-splitting-excel-workbook/