Macro to Split Up Worksheets

Here's a simple little, easy to run macro in Excel which will split up individual worksheets into separate Excel files named with the original workbook name and the worksheet name. This can be an important tool when you're processing lots of Excel files and need to run other macros on them that will on function on individual worksheets.

Sub SplitSheets() Dim W As Worksheet For Each W In Worksheets W.SaveAs ActiveWorkbook.Path & "/" & Trim(ActiveWorkbook.Name) & "_" & W.Name & ".csv", FileFormat:=xlCSV Next W End Sub

This macro is available on the site of Mr. Excel, at http://www.mrexcel.com/forum/excel-questions/73111-split-workbook-into-new-file-each-worksheet.html

You should not have to edit this macro in any way.

We begin with a spreadsheet containing three separate worksheets, "Teams"; "HallofFame"; and "Sheet2".

Press ALT + F11 to go into Visual Basic and right click on the workbook name and insert a new module. Paste in the SplitSheets macro

Go to the View ribbon, Macros . . . View Marcos and select & run the SplitSheets macro.

As you can see in the folder in which the original spreadsheet was saved, new .csv files will be generated with the original workbook name, and then the worksheet name. However a flaw in the macro adds in the names of any preceding worksheets in the workbook. If necessary this can be remedied with a REN batch file, or the method employing Bulk Rename Utility profiled on Amy Bowser Rollins' Litigation Support Guru site.