How to Split workbook into sheets in Excel

Splitting many workbooks in an excel sheet to a different worksheet is a most tedious job. if we want to split the sheet1, sheet2, sheet3 ...

Splitting many workbooks in an excel sheet to a different worksheet is a most tedious job. if we want to split the sheet1, sheet2, sheet3 in different workbook then we have to manually open a new workbook and move and copy worksheet.

We have one VBA code which will do all the work for you with a click and create a workbook from many numbers of worksheets. it will create Sheet1, sheet2, sheet3 workbook.

Below is given VBA code

[Sub breakworkbookintosheets()

MyPath = ThisWorkbook.Path

For Each sht In ThisWorkbook.Sheets

sht.Copy

ActiveSheet.Cells.Copy

ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues

ActiveSheet.Cells.PasteSpecial Paste:=xlPasteFormats

ActiveWorkbook.SaveAs _

Filename:=MyPath & "\" & sht.Name & ".xlsx"

ActiveWorkbook.Close savechanges:=False

Next sht

End Sub]

For this VBA code, open excel file go to developer tab ( if no developer tab is shown then go to excel option from file tab then go to the popular tab and click on checkbox show developer tab in ribbon)
in an alternative way press Alt+F11 >> then insert a new Module then copy above code and paste here. then run this code.
Download The Code
worksheet split will be done.