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

We have one vba code which will do all the work for you with a click and create workbook from a n number 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  
 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 popular tab and click on check box show developer tab in ribbon)
in a 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.

Post a Comment