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
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)
[Sub breakworkbookintosheets()MyPath = ThisWorkbook.PathFor Each sht In ThisWorkbook.Sheetssht.CopyActiveSheet.Cells.CopyActiveSheet.Cells.PasteSpecial Paste:=xlPasteValuesActiveSheet.Cells.PasteSpecial Paste:=xlPasteFormatsActiveWorkbook.SaveAs _Filename:=MyPath & "\" & sht.Name & ".xlsx"ActiveWorkbook.Close savechanges:=FalseNext shtEnd Sub]
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.