加入developer VB Code
- Right click anywhere on the ribbon, and then click Customize the Ribbon.
- Under Customize the Ribbon, on the right side of the dialog box, select Main tabs (if necessary).
- Check the Developer check box.
- Click OK.
- You can find the Developer tab next to the View tab.
將幾萬條RECORD 分開不同SHEET:
Developer --> VB --> Insert Module
Sub test()
Dim lastRow As Long, myRow As Long, mySheet As Worksheet
lastRow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For myRow = 2 To lastRow Step 900
Set mySheet = Worksheets.Add
Sheets("Sheet1").Rows(myRow & ":" & myRow + 899).EntireRow.Copy mySheet.Range("A1")
Next myRow
End SubRun Module
分開不同XLS :
1. Select the sheets in the Sheet tab bar, right click, and select Move or Copy from the context menu. See screenshot:
Note: Holding Ctrl key, you can select multiple nonadjacent sheets with clicking them one by one in the Sheet tab bar; holding Shift key, you can select multiple adjacent sheets with clicking the first one and the last one in the Sheet tab bar.
2. In the Move or Copy dialog, select (new book) from the To book drop down list, check the Create a copy option, and click the OK button. See screenshot:
3. Now all selected sheets are copied to a new workbook. Click File > Save to save the new workbook.
Split A Workbook To Separate Excel Files With VBA Code
The following VBA code can help you quickly split multiple worksheets of current workbook to separate Excel files, please do as follows:
1. Create a new folder for the workbook that you want to split, because the split Excel files will be stayed at the same folder as this master workbook.
2. Hold down the ALT + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.
3. Click Insert > Module, and paste the following code in the Module Window.
VBA: Split a workbook into multiple workbooks and save in the same folder
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
Sub Splitbook()'Updateby20140612Dim xPath As StringxPath = Application.ActiveWorkbook.PathApplication.ScreenUpdating = FalseApplication.DisplayAlerts = FalseFor Each xWs In ThisWorkbook.Sheets xWs.Copy Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xlsx" Application.ActiveWorkbook.Close FalseNextApplication.DisplayAlerts = TrueApplication.ScreenUpdating = TrueEnd Sub |
4. Press the F5 key to run this code. And the workbook is split to separate Excel files in the same folder with the original workbook. See screenshot:
Note: If one of the sheets has the same name with the workbook, this VBA cannot work.
Split A Workbook To Separate Excel / PDF / CSV / TXT Files With Kutools For Excel Easily
If you have Kutools for Excel installed, its Split Workbook tool can split multiple worksheets as separate Excel files conveniently and quickly with only a few clicks.
Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now
1. After installing Kutools for Excel, click Kutools Plus > Split Workbook , see screenshot:
2. In the Split Workbook dialog box, do the following operations:
(1) All worksheet names are checked by default. If you don’t want to split some of the worksheets, you can uncheck them;
(2) Check the Save a type option;
(3) From the Save as type drop down, choose one file type you want to split and save.
(4) Then click Split button.
Note: If you want to avoid splitting the hidden or blank worksheets, you can check the Skip hidden worksheets or Skip blank worksheets box.
3. In the Browse For Folder dialog, please specify a destination folder to save the split separate files, and click the OK button.
Now the checked worksheets are saved as new separated workbooks. Each new workbook is named with the original worksheet name. See screenshot:
留言列表
