跳到主要内容

如何将工作表或工作簿合并或合并为一个工作表?

在日常工作中,我们可能会遇到将数百张工作表或工作簿合并为一张工作表以分析数据的问题,如果您在Excel中使用“复制和粘贴”命令,则会花费大量时间。 在本教程的此处,我将提供一些快速解决此工作的技巧。

doc合并1

快速导航

使用VBA将活动工作簿的所有工作表合并为一个工作表
通过单击将数十个工作表或工作簿合并到一个工作表/工作簿中
将两个表合并为一个,并通过单击按列更新
下载样本文件


使用VBA将活动工作簿的所有工作表合并为一个工作表

在本节中,我提供一个VBA代码,该代码将创建一个新工作表,以在运行时收集活动工作簿的所有工作表。

1.激活要合并其所有工作表的工作簿,然后按 + 打开钥匙 Microsoft Visual Basic应用程序 窗口。

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
doc合并2

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 中的复杂任务简化。

跟老鼠手和颈椎病说再见

Kutools for Excel 的 300 个高级工具解决 80% 数秒内完成 Excel 任务,让您摆脱数千次鼠标点击。

轻松处理1500个工作场景,无需浪费时间来寻找解决方案,而有很多时间来享受生活。

包括您在内,每天为80多名高效人员提高110000%的生产力。

不再受痛苦的配方和VBA的折磨,让您的大脑得到休息和愉悦的工作氛围。

30 天免费试用全功能,30 天无理由退款。

更好的身体创造更好的生活。


将两个表合并为一个,并通过单击按列更新

如果您想将两个表合并为一个表并根据一列更新数据,如下面的屏幕截图所示,则可以尝试 表格合并 实用程序 Kutools for Excel.
doc合并11

Kutools for Excel:超过 200 个方便的 Excel 插件,只需在 Excel 中单击几下即可将复杂的任务简化

免费安装 Kutools for Excel,请执行以下操作:

1。 点击 Kutools 加 > 表格合并 启用 表格合并 向导。
doc合并12

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!
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.
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.
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 ?
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.
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?
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.
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.
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.
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
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
This comment was minimized by the moderator on the site
This VBA code is an absolute lifesaver. Thank you!!
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
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
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
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?
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.
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.
This comment was minimized by the moderator on the site
Thanks for your support, Andrew, I am glad that the macro can help you.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations