1. Please download SplitSheetsToFiles archive and unzip it to HDD.
2. Import SplitSheetsToFiles.bas Module into your Excel book (Read How-To). In our SplitSheetsToFiles.xlsm sample workbook contains 3 sheets:
3. Please return to sheet in your Excel you need to split into single files.
4. Please Save your file into HDD as Macro-Enabled Workbook (.xlsm), if you use Excel 2007, 2010, 2013, etc, or Excel 97-2003 Workbook (.xls).
5. Press "ALT+F8", select "SplitSheetsToFiles" in "Macro" window and press "Run" button.
6. In a few moments all sheets from your source Excel workbook will are saved into single files at the same folder, which will contains only 1 sheet in each. Here the listing for our SplitSheetsToFiles.xlsm sample workbook
SplitSheetsToFiles.bas macros features, limitations and error-protections:
a) Macros will not work until you save file into your HDD. If you open existing Excel workbook with "xlsx" extension, import SplitSheetsToFiles.bas and run it, macros will work.
b) Macros creates single file's extension based on source file extension - for ".xlsx" will are created ".xlsx" files, for "xlsm" - "xlsm", for "xlsb" - "xlsb", for "xls" - "xls", even you use Excel 2007, 2010, 2013, etc.
c) Macros will not erase existing workbook files with same names, it will auto-numerate them until a non-existing filename to save will be found. During auto-numeration macros will not exceed 31 symbols maximum length for sheet name.
d) For Excel 2007, 2010, 2013 users in case of Run-time error '1004': "Excel cannot insert the sheets into the destination workbook, because it contains fewer rows and columns than the source workbook. To move or copy the data to the destination workbook, you can select the data, and then use Copy and Paste commands to insert it into the sheets of another workbook." please do:
Select "File" tab, -> "Options" -> "Save" -> "Save workbooks" section -> "Save files in this format:" -> "Excel Workbook (*.xlsx)" or "Excel Macro-Enabled Workbook (*.xlsm)"
Then Press OK button. Restart the Excel and repeat operation from step 2.