跳到主要内容

如何在Excel中将多个工作簿合并为一个主工作簿?

作者:技术支持 最后修改时间:2020-05-15

当您必须将多个工作簿合并到Excel中的主工作簿时,您是否曾经被困住过? 最可怕的是,您需要组合的工作簿包含多个工作表。 以及如何仅将多个工作簿的指定工作表合并到一个工作簿中? 本教程演示了几种有用的方法来帮助您逐步解决问题。


通过移动或复制功能将多个工作簿合并为一个工作簿

如果只需要合并几个工作簿,则可以使用“移动”或“复制”命令将工作表从原始工作簿手动移动或复制到主工作簿。

1.打开工作簿,您将这些工作簿合并为主工作簿。

2.在原始工作簿中选择要移动或复制到主工作簿的工作表。

笔记:

1)。 您可以选择多个不相邻的工作表,同时按住 按Ctrl 键并一一点击工作表标签。

2)。 要选择多个相邻的工作表,请单击第一个工作表标签,按住 转移 键,然后单击最后一个工作表标签以将其全部选中。

3)。 您可以右键单击任何工作表标签,然后单击 选择所有工作表 从上下文菜单中选择同时在工作簿中的所有工作表。

3.选择所需的工作表后,右键单击“工作表”选项卡,然后单击“确定”。 移动或复制 从上下文菜单中。 看截图:

4.然后 移动或复制 对话框弹出 预订 下拉菜单中,选择要移动或复制工作表的主工作簿。 选择移动以结束 前表 框,选中 建立副本 框,最后单击 OK 按钮。

然后,您可以将两个工作簿中的工作表合并为一个。 请重复上述步骤,将工作表从其他工作簿移至主工作簿。


使用VBA将多个工作簿或指定的工作簿表合并为主工作簿

如果需要将多个工作簿合并为一个,则可以应用以下VBA代码来快速实现它。 请执行以下操作。

1.将要合并的所有工作簿放在同一目录下。

2.启动一个Excel文件(此工作簿将成为主工作簿)。

3。 按 其他 + F11 键打开 适用于应用程序的Microsoft Visual Basic 窗口。 在里面 适用于应用程序的Microsoft Visual Basic 窗口中,单击 插页 > 模块,然后将以下VBA代码复制到“模块”窗口中。

VBA代码1:将多个Excel工作簿合并为一个

Sub GetSheets()
'Updated by Extendoffice 2019/2/20
Path = "C:\Users\dt\Desktop\dt kte\"
Filename = Dir(Path & "*.xlsx")
  Do While Filename <> ""
  Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
     For Each Sheet In ActiveWorkbook.Sheets
     Sheet.Copy After:=ThisWorkbook.Sheets(1)
  Next Sheet
     Workbooks(Filename).Close
     Filename = Dir()
  Loop
End Sub
	

笔记:

1.上面的VBA代码在合并后将保留原始工作簿的图纸名称。

2.如果要区分主工作簿中的哪些工作表是合并后来自何处,请应用下面的VBA代码2。

3.如果仅要将工作簿的指定工作表合并到主工作簿中,则下面的VBA代码3可以提供帮助。

在VBA代码中,“C:\ Users \ DT168 \ Desktop \ KTE \”是文件夹路径。 在VBA代码3中,Sheet1,Sheet3”是您将合并到主工作簿的工作簿的指定工作表。您可以根据需要进行更改。

VBA代码2:将工作簿合并为一个(每个工作表将以其原始文件名的前缀命名):

Sub MergeWorkbooks()
'Updated by Extendoffice 2019/2/20
Dim xStrPath As String
Dim xStrFName As String
Dim xWS As Worksheet
Dim xMWS As Worksheet
Dim xTWB As Workbook
Dim xStrAWBName As String
On Error Resume Next
xStrPath = "C:\Users\DT168\Desktop\KTE\"
xStrFName = Dir(xStrPath & "*.xlsx")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set xTWB = ThisWorkbook
Do While Len(xStrFName) > 0
    Workbooks.Open Filename:=xStrPath & xStrFName, ReadOnly:=True
    xStrAWBName = ActiveWorkbook.Name
    For Each xWS In ActiveWorkbook.Sheets
    xWS.Copy After:=xTWB.Sheets(xTWB.Sheets.Count)
    Set xMWS = xTWB.Sheets(xTWB.Sheets.Count)
    xMWS.Name = xStrAWBName & "(" & xMWS.Name & ")"
    Next xWS
    Workbooks(xStrAWBName).Close
    xStrFName = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

VBA代码3:将工作簿的指定工作表合并到主工作簿中:

Sub MergeSheets2()
'Updated by Extendoffice 2019/2/20
Dim xStrPath As String
Dim xStrFName As String
Dim xWS As Worksheet
Dim xMWS As Worksheet
Dim xTWB As Workbook
Dim xStrAWBName As String
Dim xI As Integer
On Error Resume Next

xStrPath = " C:\Users\DT168\Desktop\KTE\"
xStrName = "Sheet1,Sheet3"

xArr = Split(xStrName, ",")

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set xTWB = ThisWorkbook
xStrFName = Dir(xStrPath & "*.xlsx")
Do While Len(xStrFName) > 0
Workbooks.Open Filename:=xStrPath & xStrFName, ReadOnly:=True
xStrAWBName = ActiveWorkbook.Name
For Each xWS In ActiveWorkbook.Sheets
For xI = 0 To UBound(xArr)
If xWS.Name = xArr(xI) Then
xWS.Copy After:=xTWB.Sheets(xTWB.Sheets.count)
Set xMWS = xTWB.Sheets(xTWB.Sheets.count)
xMWS.Name = xStrAWBName & "(" & xArr(xI) & ")"
Exit For
End If
Next xI
Next xWS
Workbooks(xStrAWBName).Close
xStrFName = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

4。 按 F5 键来运行代码。 然后,将某个文件夹中工作簿的所有工作表或指定工作表立即合并到一个主工作簿中。


轻松将多个工作簿或指定的工作簿表合并为一个工作簿

幸运的是 结合 的工作簿实用程序 Kutools for Excel 使将多个工作簿合并为一个更加容易。 让我们看看如何在组合多个工作簿时使该功能发挥作用。

申请前 Kutools for Excel首先下载并安装.

1.创建一个新的工作簿,然后单击 Kutools 加 > 结合。 然后会弹出一个对话框,提醒您所有组合的工作簿都应保存,并且该功能不能应用于受保护的工作簿,请单击 OK 按钮。

2.在 合并工作表 向导,选择 将工作簿中的多个工作表合并到一个工作簿中 选项,然后单击 下一页 按钮。 看截图:

3.在 合并工作表-第2步,共3步 对话框中,单击 添加 > 文件 or 要添加Excel文件,您将合并为一个。 添加Excel文件后,点击 完成 按钮,然后选择一个文件夹来保存主工作簿。 看截图:

现在,所有工作簿都合并为一个。

与以上两种方法相比, Kutools for Excel 具有以下优点:

  • 1)所有工作簿和工作表都在对话框中列出;
  • 2)对于要从合并中排除的工作表,只需取消选中它;
  • 3)空白工作表被自动排除;
  • 4)合并后,原始文件名将作为工作表名称的前缀添加;
  • 有关此功能的更多功能, 请访问这里.

  如果您想免费试用(30天)此实用程序, 请点击下载,然后按照上述步骤进行操作。


Kutools for Excel- 帮助您始终提前完成工作,有更多时间享受生活
您是否经常发现自己正在赶上工作,缺乏时间为自己和家人度过?  Kutools for Excel 可以帮你处理 80% Excel 拼图,提高 80% 的工作效率,让您有更多时间照顾家人,享受生活。
适用于300种工作场景的1500种高级工具使您的工作比以往更加轻松。
从现在起,不再需要记住公式和VBA代码,让您的大脑休息一下。
复杂和重复的操作可以在几秒钟内一次性完成。
每天减少成千上万的键盘和鼠标操作,现在告别职业病。
在3分钟内成为Excel专家,帮助您快速获得认可并提薪。
110,000名高效人才和300多家世界知名公司的选择。
使您的$ 39.0的价值超过$ 4000.0的他人培训。
全功能免费试用 30 天。 60 天无理由退款保证。

Comments (146)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I have one workbook with 100+ sheets, I want to move all 100+ sheets into another workbook in a single sheet.
This comment was minimized by the moderator on the site
I had to read throught the comments to find suggestions that worked for my application of the VBA CODE 2, but I managed to get it to work doing the following things:
1. make sure to change "C:\Users\DT168\Desktop\KTE\" to your own directory to wherever you have your files are located. don't forget the extra "\" at the end!2. my spreadsheets were extension ".xls", so I deleted the extra "x" in this line, like so: xStrFName = Dir(xStrPath & "*.xlsx")3. I placed all my spreadsheets in a single folder, and only those files were the contents of that folder, the target macro enabled spreadsheet where this vba was running from was saved outside of this folder (hopefully this makes sense).
one thing that I didn't want to mess with though is I only needed to merge the 1st tab of each spreadsheet, but I didn't want to mess with the code so if each workbook you want to merge into one has multiple tabs, this code will grab all of the tabs on each workbook and place them in your target spreadsheet, I had to manually delete all the tabs I didn't want.
if the author of this vba could reply to me, how do you change the code to just copy the 1st tab as opposed to all the tabs?
thank you!
This comment was minimized by the moderator on the site
hi I want a change. If the the sheet name is same then the data should be appended in the same name sheet rather than adding a sheet. for example if i have 10 files with jan, feb, mar same sheetnames. then result should be 1 file having jan, feb, mar only 3 sheets with the data of all 10 files. thanks
This comment was minimized by the moderator on the site
Good morning,

Basically I have to copy the values of another file example c: \ test.xlsx (sheet name "date"):

I have to copy the values from A2: T20


And I have to paste in another Extract.xlsx file on the “Extracts” folder on A2.


PLEASE NOTE: You must run vba when opening the file.
This comment was minimized by the moderator on the site
Hello! I need to merge multiple files into one, that are password protected. All source files use the same password. What changes are needed to the first VBA script to merge the files without having to enter the password each time?
This comment was minimized by the moderator on the site
Hello any one can help me, I want to combine sheet 2 only from 5 different sheet, can you script for me.
This comment was minimized by the moderator on the site
you can use Array function to copy the multiple sheets to combine in one file
Sheets(Array("Sheet1","Sheet2")).copy
This comment was minimized by the moderator on the site
hai sir i want know code for copying multiple sheets in one excel to multiple excels
This comment was minimized by the moderator on the site
Sheets(Array("Sheet1","Sheet2")).copy
This comment was minimized by the moderator on the site
hai sir i want know code for copying multiple sheets in one excel to multiple excels
This comment was minimized by the moderator on the site
how to use VBA code 1 to amend and make it runs to combine all the .xlsx files into one excel file and each excel spreedsheet tab in the combined excel file should be named as original file name.thanks
This comment was minimized by the moderator on the site
What part of VBA code 3 specifies the name of the worksheet to be copied?
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations