Question:

This question already has an answer here:

<ul><li> <a href="/questions/59075/save-each-sheet-in-a-workbook-to-separate-csv-files" dir="ltr" rel="nofollow">Save each sheet in a workbook to separate CSV files</a> <span class="question-originals-answer-count"> 6 answers </span> </li> </ul>

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?

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 <a href="https://msdn.microsoft.com/en-us/library/office/ff198017.aspx" rel="nofollow">here</a>