Merge multiple files in Excel

Create a worksheet, name it, and save it to the same file folder as the merged 100 files. press Alt + F11 and double-click sheet1 (sheet1) in the project resource manager ), paste the following code in the code area on the right. Run. Wait for a while and then OK.

Sub: merge all worksheets of all workbooks in the current directory ()
Dim mypath, myname, awbname
Dim WB as workbook, wBN as string
Dim G as long
Dim num as long
Dim box as string
Application. screenupdating = false
Mypath = activeworkbook. Path
Myname = Dir (mypath & "\" & "*. xls ")
Awbname = activeworkbook. Name
Num = 0
Do While myname <> ""
If myname <> awbname then
Set WB = workbooks. Open (mypath & "\" & myname)
Num = num + 1
With workbooks (1). activesheet
. Cells (. Range ("a65536"). End (xlup). Row + 2, 1) = left (myname, Len (myname)-4)
For G = 1 to sheets. Count
WB. Sheets (g). usedrange. Copy. cells (. Range ("a65536"). End (xlup). Row + 1, 1)
Next
WBN = wBN & CHR (13) & WB. Name
WB. Close false
End
End if
Myname = dir
Loop
Range ("A1"). Select
Application. screenupdating = true
Msgbox combines all worksheets in the "& num &" workbook. As follows: "& CHR (13) & wBN, vbinformation," prompt"
End sub