M-TechRidge Blog

How to Split sheets to individual workbooks

Change Language:

Split sheets to individual workbooks. Export and save each worksheet as separate new workbooks. Split sheets into different workbooks.Split Each Excel Sheet Into Separate Files (Step-by-Step).

1. Open excel workbook and Open Visual Basic for applications by pressing ALT+F11. In this window goto insert menu than Module option.

2. Now copy and paste below given code to module window.

Sub SplitWorkbook()

Dim FileExtStr As String

Dim FileFormatNum As Long

Dim xWs As Worksheet

Dim xWb As Workbook

Dim xNWb As Workbook

Dim FolderName As String

Application.ScreenUpdating = False

Set xWb = Application.ThisWorkbook

DateString = Format(Now, "yyyy-mm-dd hh-mm-ss")

FolderName = xWb.Path & "" & xWb.Name & " " & DateString

If Val(Application.Version) < 12 Then

   FileExtStr = ".xls": FileFormatNum = -4143


   Select Case xWb.FileFormat

       Case 51:

           FileExtStr = ".xlsx": FileFormatNum = 51

       Case 52:

           If Application.ActiveWorkbook.HasVBProject Then

               FileExtStr = ".xlsm": FileFormatNum = 52


               FileExtStr = ".xlsx": FileFormatNum = 51

           End If

       Case 56:

           FileExtStr = ".xls": FileFormatNum = 56

       Case Else:

           FileExtStr = ".xlsb": FileFormatNum = 50

       End Select

End If

MkDir FolderName

For Each xWs In xWb.Worksheets

On Error GoTo NErro

   If xWs.Visible = xlSheetVisible Then



   xFile = FolderName & "" & xWs.Name & FileExtStr

   Set xNWb = Application.Workbooks.Item(Application.Workbooks.Count)

   xNWb.SaveAs xFile, FileFormat:=FileFormatNum

   xNWb.Close False, xFile

   End If




   MsgBox "You can find the files in " & FolderName

   Application.ScreenUpdating = True

End Sub

3. Now Click on Run button and wait for a while. After that goto excel file location. There will be a folder with same name as excel file. Open it and you will see all sheets are separately exported successfully .

Watch Full Video Tutorial in English:

Watch Full Video Tutorial in Hindi:

Leave a Comment:

Validation code:

Can't read the image? click here to refresh.