When working with Excel, you may want to separate worksheets into different files. There are different methods to separate them. If you have only a couple of sheets in the workbook, you can use the "Move and Copy" command to separate them one by one. However, when you have many worksheets in the workbook, you may need to think of some easier ways by using the macro codes. Please see below for details:
Step 1: Create a new folder and move the workbook into the folder. The reason is that the separated files will show up in the folder;
Step 2: Pressing shortcut "Alt+F11" to open the Microsoft Visual Basic for Applications window;
Alternatively, please click the "Developer" tab from the ribbon and click "Visual Basic" to open the window.
Step 3: In the new window, click the "Insert" tab from the ribbon, and click "Module";
Step 4: Copy and paste the following codes in the Module window;
Sub Splitbook() Dim xPath As String xPath = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False For Each xWs In ThisWorkbook.Sheets xWs.Copy Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xlsx" Application.ActiveWorkbook.Close False Next Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
Step 5: Click the "Run Sub" button (or press the F5 key) to run the codes;
Step 6: Go back to the folder and you will see all separated files along with the original workbook in the folder.