Macro to Split Up Worksheets

Macro to Split Up Worksheets

April 12, 2016

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. 

 

 

 

 

Please reload

Contact Me With Your Litigation Support Questions:

seankevinoshea@hotmail.com

  • Twitter Long Shadow

© 2015 by Sean O'Shea . Proudly created with Wix.com