Split Excel Sheets into different files

Need to split your xls file with multiple sheet into different file for each sheet.

Try google and you will mostly find the paid softwares to do this task. However, google deep enough and you might come up with multiple posts with VBA code - which does this for free.. and yes yes,, hold on.. most of these dont work..

But since I was able to find a working solution I am posting the same here

Scenario 1

Split Excel File having Multiple Worksheet - Each Worksheet saved as separate file. Each file will only have one sheet.

VBA Code:

Sub SplitBookIntoSheets()

Dim i As Integer

Dim ws As Worksheet

Dim CntSheets As Long

Dim FileFolder As String

FileFolder = InputBox("Where would you like these saved?", "filename", "C:\Temp\")

For i = 1 To Worksheets.Count

CntSheets = Worksheets.Count

If CntSheets <> "1" Then

Sheets(CntSheets).Move

ActiveWorkbook.SaveAs Filename:=FileFolder & ActiveSheet.Name & ".xls"

ActiveWindow.Close

Else

ActiveWorkbook.SaveAs Filename:=FileFolder & ActiveSheet.Name & ".xls"

ActiveWindow.Close

End If

Next i

End Sub

Scenario 2

Split Excel File having Multiple Worksheet - Each Worksheet saved as separate file with file name as the sheet name. However, each copy will hold all the sheets..!! not sure if you will ever need this scenario.

VBA Code:

Sub SplitSheets() Dim W As Worksheet For Each W In Worksheets W.SaveAs ActiveWorkbook.Path & "/" & W.Name Next W End Sub

Hope it helps ! :)

Comments Welcome.....