Place the Excel document you want to merge in the same folder, and then create a new Excel file in the folder to open Excel, press ALT+F11, or
Click Tools, Macro, VB Editor, pop-up editor, click Insert Module, enter the following code in the new blank window:
Sub Merge all worksheets for all workbooks in the current directory () Dim MyPath, MyName, awbname Dim Wb As Workbook, wbn as String Dim G As Long Dim N Um as Long Dim BOX as String flag=0application.screenupdating=False MyPath=Activeworkbook.path MyName= Dir (MyPath &"\ "&"*.xls")Awbname =activeworkbook.name Num=0Do While MyName<>""If MyName<>Awbname then Set Wb= Workbooks.Open (MyPath &"\ "& MyName)num = num +1With Workbooks (1). ActiveSheet for G=1To sheets.count If flag=0then//Here I have to make a judgment, if it is the first document to take the head title also, not just copy the content wb.sheets (G). Usedrange.copy. Cells (. Range ("A65536"). End (Xlup). Row,1) Flag=1Else wb.sheets (G). Range ("A2", Wb.sheets (G). Cells.specialcells (xlCellTypeLastCell)). Copy. Cells (. Range ("A65536"). End (Xlup). Row +1,1) End If Next wbn= WBN & Chr ( -) &wb.name wb.close False End with end If MyName=Dir Loop Range ("A1"). Select application.screenupdating=True MsgBox"merged together."& Num &"Worksheets under a workbook. as follows:"& Chr ( -) & Wbn, vbinformation,"Tips"End Sub
Save---Point to perform key OK!!!
Merging multiple Excel file contents