excel合并工作簿和工作表的代碼(最新整理)

1、把多個(gè)工作簿合并到一個(gè)工作簿作為新工作簿的一張表（宏代碼）sub 合并當(dāng)前目錄下所有工作簿的全部工作表() dim mypath, myname, awbnamedim wb as workbook, wbn as string dim g as longdim num as long dim box as stringapplication.screenupdating = false mypath = activeworkbook.path myname = dir(mypath & & *.xls) awbname = activeworkbook.name num = 0do whil

2、e myname if myname awbname thenset wb = workbooks.open(mypath & & myname) num = num + 1with workbooks(1).activesheet.cells(.range(a65536).end(xlup).row + 2, 1) = left(myname, len(myname) - 4) for g = 1 to sheets.countwb.sheets(g).usedrange.copy .cells(.range(a65536).end(xlup).row + 1, 1) nextwbn = w

3、bn & chr(13) & wb.name wb.close falseend with end ifmyname = dir loop range(a1).selectapplication.screenupdating = truemsgbox 共合并了 & num & 個(gè)工作薄下的全部工作表。如下： & chr(13) & wbn, vbinformation, 提示end sub具體操作：在工作簿目錄下新建一工作簿，工具-宏-編輯器-插入模塊-粘貼代碼=運(yùn)行excel 如何將一個(gè)工作簿中的多個(gè)工作表合并到一張工作表上打開你的工作簿 新建一個(gè)工作表 在這個(gè)工作表的標(biāo)簽上右鍵 查看代碼

4、你把下面的代碼復(fù)制到里邊去，然后 上面有個(gè)運(yùn)行 運(yùn)行子程序就可以了，代碼如下，如果 出現(xiàn)問題你可以嘗試工具 宏 宏安全性里把那個(gè)降低為中或者低再試試sub 合并當(dāng)前工作簿下的所有工作表() application.screenupdating = falsefor j = 1 to sheets.countif sheets(j).name activesheet.name then x = range(a65536).end(xlup).row + 1 sheets(j).usedrange.copy cells(x, 1)end if nextrange(b1).select applic

5、ation.screenupdating = truemsgbox 當(dāng)前工作簿下的全部工作表已經(jīng)合并完畢！, vbinformation, 提示end sub把同一工作簿多張工作表合并到同一張工作表1新建一個(gè)工作表放在最左邊，alt + f11 鍵打開代碼框-插入-模塊-復(fù)制以下代碼alt + f8 鍵打開，運(yùn)行該代碼即可sub 合并()for i = 2 to sheets.count如果工作表的第一行都一樣，就把下面 rows(1 &的 1 改成 2 就好了sheets(i).rows(1 & : & sheets(i).range(a60000).end(xlup).row). _ co

6、py range(a & range(a60000).end(xlup).row + 1)next end sub批量將多個(gè) excel 中的多個(gè)工作簿合并到一個(gè) excel 中將要合并的 excel 放到一個(gè)文件夾中，在這個(gè)目錄中新建一個(gè) excel,運(yùn)行以下代碼sub combinefiles()dim pathas stringdim filenameas stringdim lastcellas rangedim wkbas workbookdim wsas worksheetdim thiswbas stringdim mydir as stringmydir = thisworkb

7、ook.path & chdrive left(mydir, 1) find all the excel files chdir mydirmatch = dir$()thiswb = thisworkbook.name application.enableevents = false application.screenupdating = false path = mydirfilename = dir(path & *.xls, vbnormal) do until filename = if filename thiswb thenset wkb = workbooks.open(fi

8、lename:=path & & filename) for each ws in wkb.worksheetsset lastcell = ws.cells.specialcells(xlcelltypelastcell)if lastcell.value = and lastcell.address = range($a$1).address then elsews.copy after:=thisworkbook.sheets(thisworkbook.sheets.count) end ifnext ws wkb.close falseend iffilename = dir() lo

9、opapplication.enableevents = true application.screenupdating = trueset wkb = nothing set lastcell = nothingend sub“”“”at the end, xiao bian gives you a passage. minand once said, people who learn to learn are very happy people. in every wonderful life, learning is an eternal theme. as a professional

10、 clerical and teaching position, i understand the importance of continuous learning, life is diligent, nothing can be gained, only continuous learning can achieve better self. only by constantly learning and mastering the latest relevant knowledge, can employees from all walks of life keep up with the pace of enterprise development and innovate to meet the n