Integration of multiple tables in Excel using VBA
Requirement
Keep the column name, copy the data in each excel file, and merge it to an Excel file.
Procedure
- Put the files to be merged in the same folder and copy them. (PS: it is best not to directly operate the original data file to avoid operation failures and data loss)
- Create a merge. XLSX under this directory"
- Double-hitting merge. XLSX"
- At the same time, press Alt + F11
- Appears. merge can be completed by text operations in the figure.
- Complete
Appendix code
Sub combines all worksheets in 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 flag = 0 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 work Books (1 ). activesheet for G = 1 to sheets. count if flag = 0 then WB. sheets (g ). usedrange. copy. cells (. range ("a65536 "). end (xlup ). row, 1) Flag = 1 else 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 (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