How to Split sheets to individual workbooks

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

Else

   Select Case xWb.FileFormat

       Case 51:

           FileExtStr = ".xlsx": FileFormatNum = 51

       Case 52:

           If Application.ActiveWorkbook.HasVBProject Then

               FileExtStr = ".xlsm": FileFormatNum = 52

           Else

               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

   xWs.Select

   xWs.Copy

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

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

   xNWb.SaveAs xFile, FileFormat:=FileFormatNum

   xNWb.Close False, xFile

   End If

NErro:

   xWb.Activate

Next

   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 .