How can i split an Excel (.xls) file that contains

Question

This question already has an answer here:

• Save each sheet in a workbook to separate CSV files 6 answers

I have an excel .xls file that contains 10 sheets. I want to split all of those sheets as separate .xls files. Does excel have any option to export a sheet into a separate .xls file?

Answer 1

Try this code

Sub Splitbook()

    Dim xPath As String
    xPath = Application.ActiveWorkbook.Path
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each xWs In ThisWorkbook.Sheets
    xWs.Copy
    Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xls", fileformat:=56 
    Application.ActiveWorkbook.Close False
    Next
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

These are the main file formats in Excel 2007-2013, Note: In Excel for the Mac the values are +1

51 = xlOpenXMLWorkbook (without macro's in 2007-2013, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2013, xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007-2013 with or without macro's, xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007-2013, xls)
6 = ".csv"
-4158 = ".txt"
36 = ".prn"

All file formats codes here