Merge multiple workbooks __vba for one workbook and split workbooks for multiple workbooks

Merge multiple workbooks into one workbook

1, right to view the code, insert the following code

Worksheets in Sub workbooks merge ()


Dim FileOpen
Dim X As Integer
application.screenupdating = False
FileOpen = Application.getopenfilename (filefilter:= "Microsoft Excel File (*.xls), *.xls", Multiselect:=true, title:= "Merge Workbook")
x = 1 while
x <= UBound (FileOpen)
Workbooks.Open filename:=fileopen (x)
Sheets (). Move after:=thisworkbook.sheets (ThisWorkbook.Sheets.Count)
x = x + 1
wend
exithandler:
application.screenupdating = True
Exit Sub




errhadler:
MsgBox err.description End
Sub

2, save, the table is saved as a macro-enabled workbook, if for xlsx remember to change the above. xls

3. Development tools Run macros

split a workbook into multiple workbooks

1, click the menu to insert the module, enter the following code:

Private Sub Split Worksheet ()
       Dim sht As Worksheet
       Dim MyBook as workbook
       Set MyBook = ActiveWorkbook for each
       sht I n mybook.sheets
           sht. Copy
           activeworkbook.saveas Filename:=mybook.path & "\" & Sht. Name, Fileformat:=xlnormal     Save workbook as Excel default format
           activeworkbook.close
       Next
       MsgBox "file has been split!"
   End Sub

2. Click Run Run sub Process | form, split file with source file at same address.