如何將工作表或工作簿合併或合併為一個工作表？

在我們的日常工作中，我們可能會遇到將數百張工作表或工作簿合併為一張工作表以分析數據的問題，如果您在Excel中使用“複製和粘貼”命令，則該過程將花費大量時間。在本教程的此處，我將提供一些快速解決此工作的技巧。

快速導航

使用VBA將活動工作簿的所有工作表合併為一個工作表
 單擊將數十個工作表或工作簿合併到一個工作表/工作簿中
 將兩個表合併為一個，並通過單擊按列更新
 下載樣本文件

使用VBA將活動工作簿的所有工作表合併為一個工作表

在本節中，我提供一個VBA代碼，該代碼將創建一個新工作表，以在運行時收集活動工作簿的所有工作表。

1.激活要合併其所有工作表的工作簿，然後按 + 打開鑰匙 Microsoft Visual Basic for Applications 窗口。

2.在彈出的窗口中，單擊插入 > 模塊創建一個新的模塊腳本。

3.複製以下代碼並將其粘貼到腳本中。

Sub Combine()
'UpdatebyExtendoffice
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub

4。按 F5 鍵，則工作表中的所有數據都已合併到名為“合併”的新工作表中，該新工作表位於所有工作表的前面。

您想加薪並有很多時間陪伴家人嗎？

Office選項卡可將Microsoft Office立即工作的效率提高50％

令人難以置信的是，處理兩個或多個文檔比處理一個或多個文檔更容易，更快捷。

與知名瀏覽器相比，Office Tab中的選項卡式工具功能更強大，更高效。

每天為您減少數百次鼠標單擊和鍵盤輸入，現在告別鼠標手。

如果您通常處理多個文檔，Office Tab將為您節省大量時間。

30-天免費試用，無需信用卡。

閱讀更多現在免費下載

單擊將數十個工作表或工作簿合併到一個工作表/工作簿中

使用VBA，您只能在活動工作簿中合併工作表，但是如何將工作簿中的工作表合併到工作表或工作簿中？

將工作簿中的工作表合併為一張工作表
doc合併18

將工作簿中的工作表合併到一個工作簿中
doc合併3

為了解決這項工作並滿足對紙張組合的其他要求，結合功能已通過以下四種組合方案開發：

將多張工作表或工作簿合併為一張
將多個工作表或工作簿合併為一個工作簿
將相同的姓名表合併為一張
將工作表或工作簿中的值合併為一張工作表

這里以第二個選項為例：

將多個工作表或工作簿合併為一個工作簿

後免費安裝 Kutools for Excel，請執行以下操作：

1.激活Excel，單擊 Kutools 加 > 結合，會彈出一個對話框，提醒您要合併的工作簿需要關閉。請點擊 OK 以繼續。
doc合併4
doc合併5

2。在裡面 合併工作表步驟1 對話框，檢查 將工作簿中的多個工作表合併到一個工作簿中 選項。點擊 下一頁 轉到嚮導的下一步。
doc合併6

3。點擊加入 > 文件 or 夾將您要合併的工作簿添加到 工作簿清單 窗格，然後您可以通過檢查名稱來指定將哪些工作表連接在一起 工作表清單 窗格。請點擊 下一頁 轉到嚮導的最後一步。
doc合併7

4.在此步驟中，根據需要指定設置。然後點擊完.
doc合併8

5.彈出一個窗口，供您選擇一個文件夾以放置組合的工作簿，然後單擊節省.
doc合併9

現在，工作簿已合併到一個工作簿中。並且在所有工作表的最前面，還創建了一個名為Kutools for Excel的主工作表，其中列出了有關工作表的一些信息以及每個工作表的鏈接。
doc合併10

演示：將工作表/工作簿合併為一個工作表或工作簿

Kutools for Excel：200 +有用的便捷工具，只需單擊幾下即可簡化Excel中的複雜任務。

告別鼠標手和頸椎病

300個高級工具 Kutools for Excel 解決企業排放佔全球 80% 數秒內完成 Excel 任務，讓您擺脫數千次鼠標點擊。

輕鬆應對1500個工作場景，無需浪費時間尋找解決方案，有更多時間享受生活。

每天為 80+ 高效能人士提高 110000% 的生產力，當然也包括你。

不再被痛苦的公式和VBA折磨，給你的大腦一個休息和快樂的工作心情。

30 天免費試用全功能，30 天無理由退款。

更好的身體創造更好的生活。

將兩個表合併為一個，並通過單擊按列更新

如果您想將兩個表合併為一個表並根據一列更新數據，如下面的屏幕截圖所示，可以嘗試 表格合併 的效用 Excel的Kutools.
doc合併11

Kutools for Excel：超過200個方便的Excel加載項，可將復雜的任務簡化為在Excel中的幾次單擊

後免費安裝 Kutools for Excel，請執行以下操作：

1。點擊 Kutools 加 > 表格合併 啟用 表格合併 嚮導。
doc合併12

Excel的Kutools

2.在嚮導的步驟1中，您需要分別選擇主表和查找表範圍。然後點擊 下一頁.
doc合併13

3.在主表上檢查要更新數據的鍵列。請點擊 下一頁.
doc合併14

4.然後檢查您要基於查找表更新數據的主表中的列。請點擊 下一頁.
doc合併15

5.在嚮導的步驟4中，檢查要從查找表添加到主表的列。請點擊 下一頁.
doc合併16

6.在嚮導的最後一步，根據需要指定設置選項。然後點擊完.
doc合併17

現在，主表已更新數據並基於查找表添加新數據。

演示：將工作表/工作簿合併為一個工作表或工作簿

Kutools for Excel：200 +有用的便捷工具，只需單擊幾下即可簡化Excel中的複雜任務。

下載樣本文件

推薦的生產力工具

Office選項卡-Microsoft Office 2019-2003和Office 365中的選項卡式瀏覽，編輯，文檔管理

一個用於加速Excel 2019-2007的專業加載項，可將小時任務縮減為幾秒鐘

此外接程序包括數十個專業組，具有300多個選項，可以自動執行Excel中的大多數日常任務，並至少使生產率提高50％。例如一組一鍵式opton和批量轉換。
現在，您可以使用Kutools for Excel加快自己的速度！

Comments (168)

No ratings yet. Be the first to rate!

rd2022

about 2 years ago

#37027

This comment was minimized by the moderator on the site

Hi, I have a system that generates an excel file everyday. The daily file name is of format "<filename-12282021>". The 12282021 is the timestamp and it changes everyday creating a new excel file each day. I want to create a Master Workbook and then append the contents of the daily excel file into that master workbook. How can I automate it so it happens by itself everyday and the master file is update with contents of the daily file. Thank you.

Report

Sunny rd2022

about 2 years ago

#37028

This comment was minimized by the moderator on the site

Hi rd2022, sorry that I have no idea to solve your problem so far. You can go to our forum https://www.extendoffice.com/forum/excel.html to leave this message, maybe some users have the same problem and has been solved.

Report

AJIT DIXIT

about 3 years ago

#33468

This comment was minimized by the moderator on the site

In combining shhets I want to add one extra column in combined sheet as ShhetName
Can you suggest ?

Sunny AJIT DIXIT

about 3 years ago

#33469

This comment was minimized by the moderator on the site

The Combine function of Kutools for Excel can help you to insert sheet name in a new row of each combined range in the combined sheet by checking First row of each range (new row) in Insert worksheet information section in the last step of the Combine.

View Attachments (1 / 5)

Report

Eben

about 3 years ago

#33013

This comment was minimized by the moderator on the site

Hi - this function works well except my file has more than 900 sheets, and when I change the maximum number of rows (Loc 16, "A65536") to a higher number, the macro doesn't work. Is there a way to increase the number of rows that can be shown on the combine sheet?

Charlotte

about 4 years ago

#32234

This comment was minimized by the moderator on the site

The VBA code works fine but I want the data to be merged horizontally(side by side).
Hope you can help.

Daniel

about 4 years ago

#30793

This comment was minimized by the moderator on the site

Hi,

Great Code and for the most part it works fantastically well.

Could I suggest creating a written tutorial for each line of code. I'd love to be able to play around with it to suit my Company's needs.

In the meantime however could you talk me through problems that would hinder this code from copying all data from each Sheet? I've noticed some sheets in my workbook aren't being copied at all.

To give you some context I'm dealing with roughly 330 sheets and they hold from 50 to 500 lines in each.

looking forward to hearing from you.

Sunny Daniel

about 4 years ago

#30794

This comment was minimized by the moderator on the site

Hi, Daniel, I do not understand your question as you describe it not clearly, but if you have the Combine function in Excel, it can solve most of your question about combining sheets.

Report

about 4 years ago

#30430

This comment was minimized by the moderator on the site

Hi,

Is it possible to only copy a particular range of cells for example A:4:FU38 from each sheet?

Also in the Combined sheet where the data is copied to, can the tab names from where the data is copied included in column A to enable lookups or to pivot the data?

Finally, can i specify the list of worksheets from which to copy the data across or maybe i can specify something like copy all worksheets to the left of a particular worksheet in the file.

Thanks in advance for your help

Sunny HS

about 4 years ago

#30431

This comment was minimized by the moderator on the site

Hi, HS, you mean to combine same ranges from sheets into one sheet, it that right? If so, this article may help you: https://www.extendoffice.com/documents/excel/2754-excel-combine-ranges-from-different-worksheets-into-one.html#a3

Report

Dennis

about 4 years ago

#29809

This comment was minimized by the moderator on the site

This VBA code is an absolute lifesaver. Thank you!!

martinoreilly

about 5 years ago

#28049

This comment was minimized by the moderator on the site

Hi. This code works well but I have the same problem as adj. The 1st row form each tab doesn't appear in the combined tab - except he first tab. Thanks

Report

Pete martinoreilly

about 3 years ago

#28051

This comment was minimized by the moderator on the site

Just delete "Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select" from the code.

If you want to merge all rows from all worksheets, just use the code:

Sub Combine()

'UpdatebyExtendoffice

Dim J As Integer

On Error Resume Next

Sheets(1).Select

Worksheets.Add

Sheets(1).Name = "Combined"

Sheets(2).Activate

Range("A1").EntireRow.Select

Selection.Copy Destination:=Sheets(1).Range("A1")

For J = 2 To Sheets.Count

Sheets(J).Activate

Range("A1").Select

Selection.CurrentRegion.Select

Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)

Next

End Sub

Sunny martinoreilly

about 5 years ago

#28050

This comment was minimized by the moderator on the site

Yes, the code only can keep first row of the first tab, because it is used to consolidate, if you want to keep all contents of each sheet, you can try Combine utility of Kutools for Excel, it is free fior 60 days

Report

elok

about 5 years ago

#27845

This comment was minimized by the moderator on the site

i had a problem when I try to combine all same name worksheet to one worksheet, it works but some value are missing. so what should i do?

Sunny elok

about 5 years ago

#27846

This comment was minimized by the moderator on the site

Hello, elok, did the problem appear while you applying Combine function? If so, please go to contact us to describe the problem with more details, our support will handle the problem for you.

Report

Andrew

about 5 years ago

#27629

This comment was minimized by the moderator on the site

Thanks that macro saved my day. I had over 40 sheets of data to combine into one and although my computer bogged for a minute or two, it completed without issue. The only modification I made was I tweaked the macro to start with A2 instead of A1 since A1 was my column headers.

Sunny Andrew

about 5 years ago

#27630

This comment was minimized by the moderator on the site

Thanks for your support, Andrew, I am glad that the macro can help you.

Report

There are no comments posted here yet

Please leave your comments in English

Posting as Guest

Name (Required)

Email (Required)

Rate this post:

Reset

0 Characters

更新日誌

功能教程

得到幫助？