How to combine multiple Excel tables into one

Combine multiple worksheets into one worksheet in an Excel worksheet _ combine multiple tables in one worksheet into one

Create a worksheet, name it, and save it to the same file folder as the merged n files. press Alt + F11, double-click sheet1, and paste the following code in the code area on the right. Run.

Sub: merge all worksheets of all workbooks in the current directory ()

Dim mypath, myname, awbnamedim WB as workbook, wBN as stringdim G as longdim num as longdim 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 ("A6 5536 "). 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 with 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

Sub combines all worksheets in the current workbook () application. screenupdating = falsefor j = 1 to sheets. count ifsheets (j ). name <> activesheet. name then x = range ("a65536 "). end (xlup ). row + 1 Sheets (j ). usedrange. copy cells (x, 1) end ifnextrange ("B1 "). selectapplication. screenupdating = truemsgbox "All worksheets under the current workbook have been merged! ", Vbinformation," prompt "End sub

Or use this to try.

Create a blank workbook, press Alt + F11 to bring up its VBA window, insert a user module, and paste the following code.

Sub merge Workbook () application. displayalerts = false' close the prompt window shes = application. sheetsinnewworkbook 'workbook contains the number of worksheets application. in the new workbook generated by sheetsinnewworkbook = 1', only one worksheet is setnewbok = workbooks. add 'generate new workbook setnewshe = newbok. worksheets (1) 'new worksheet S = 1' write data from the first row of the new worksheet NA = Dir ("D: \ time \*. xls ") 'All worksheets to be merged must be saved in the time folder of disk D in advance. dowhilena <>" "setwb = application. workbooks. open ("D: \ time \" & Na) WB. worksheets (1 ). usedrange. copy 'Copy data newbok. activatecells (s, 1 ). selectactivesheet. paste 'execute paste S = newshe. usedrange. rows. count + 1 cells (s, 1) = WB. name' The Name Of The workbook to which the data is written. S = S + 1wb. close 'close workbook NA = Dir () 'to get the next workbook

LoopApplication.SheetsInNewWorkbook=shesApplication.DisplayAlerts=TrueRange("a1").SelectEndSub

Execute this code to quickly complete the merging.

How to combine multiple Excel tables into one