Split an Excel File – One file per worksheet

* Save the following macro into your personal macro workbook (PERSONAL.XLS), you may need to enable the developer ribbon first.

Sub SaveEachWS()

Dim ws As Worksheet

Dim loc As String

Dim loc2 As String

loc = InputBox(“Location To Save Files eg  c:\folder”)

loc2 = loc

If Mid(StrReverse(loc), 1, 1) = “\” Then

loc = StrReverse(Mid(StrReverse(loc), 2, 9999))

End If

For Each ws In ActiveWorkbook.Worksheets


loc2 = loc & “\” & ws.Name & “.xls”

ActiveWorkbook.SaveAs Filename:=loc2


Next ws

End Sub

*Open the file you want to split and run the macro SaveEachWS from the macro menu. You will be asked to provide a location, this location must exist, the macro won’t create folders on the fly.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: