How to combine multiple Excel tables into one

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.