提示:其他语言是Google翻译的。 你可以访问 English 版本。
登录
x
or
x
x
注册账户
x

or

如何将多个工作簿合并到Excel中的一个主工作簿中?

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


在Excel中轻松将多个工作簿合并为一个:

Acker 结合 工作簿的实用性 Kutools for Excel 可以帮助您轻松地将多个Excel文件合并为一个。 此外,它允许您只轻松地将某些Excel文件的指定工作表组合到主工作簿。 立即下载60-day免费试用版!

使用“移动”或“复制”功能将多个工作簿合并到一个工作簿中

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

1。 打开要合并到主工作簿中的工作簿。

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

备注:

1)。 您可以通过按住选择多个不相邻的工作表 按Ctrl 键,然后逐个单击工作表标签。

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

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

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

4。 那么 移动或复制 弹出对话框,在 预订 下拉列表,选择要移动的主工作簿或将工作表复制到其中。 选择移动到结尾 在表单之前 框,检查 创建一个副本 框,最后点击 OK 按钮。

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

Office选项卡 - 实时节省外接程序!

在一个窗口中显示所有打开的文档,以帮助快速查找所需的文档。

全功能免费试用45天。 兼容每个版本的Excel,Word和PowerPoint。

了解更多关于ADD-IN>

使用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 & "*.xls")
  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:\用户\ DT168 \桌面\ 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

它是一个方便的Excel加载项,它收集了超过300高级功能,以简化您在Excel操作中的工作。 有了它,您可以轻松地:

  • 结合多工作簿;
  • 按颜色计算/求和单元格;
  • 分裂细胞;
  • 批量删除空格;
  • 更多功能......

立即免费下载!

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

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

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

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

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

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

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

做这个 结合工作簿 实用帮助? 让我们下载并试用60-day全功能免费试用版!

适用于Microsoft Office的推荐生产力工具

Office选项卡 - 在Excel / Word / PowerPoint中选项卡式浏览,编辑和管理:

Office Tab 将谷歌浏览器,Internet Explorer新版本和Firefox等Web浏览器中的选项卡界面带到Microsoft Excel / Word / PowerPoint。 它将是一个节省时间的工具,在您的工作中无可比拟。 见下面的演示:

ot1

Kutools for Excel - 结合Microsoft Excel的300高级功能和工具:

Kutools for Excel 是一个方便的Excel加载项,具有超过300高级功能,可以在Excel中点击几下简化各种复杂任务。 例如:

  • 将多个工作表或工作簿合并到一个工作簿中
  • 根据背景/字体颜色对单元格进行计数和求和
  • 通过空格/逗号/分隔符将单元格内容拆分为多行/列
  • 批量删除单元格中的前导空格
观塘线延线

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    cindy · 2 years ago
    thanks for the macro guided for 'Combine multiple workbooks to one workbook with VBA'.

    However, i do have an additional cirtumtances.
    Let's take your example.
    In the folder 'dt kte', there are 4 workbook (book1, book2, book3 & book4)
    The macro will merge all worksheet in every single workbook into my excel file.
    However, the circumtances i need is:-
    in every workbook, there is mutual sheet named "HIJ".

    what would be the macro if i only want to merge the HIJ worksheet in book1 to book4, into one workbook i want?
  • To post as a guest, your comment is unpublished.
    MUHAMMAD MURTAZA · 2 years ago
    ASSALAM O ALIKUM,,

    LOT OF THANKS FOR COMBINE WORK SHEET IN MASTER SHEET
  • To post as a guest, your comment is unpublished.
    Gnanesh · 2 years ago
    Thank you very much for the script for combining workbooks!
  • To post as a guest, your comment is unpublished.
    Hoang · 2 years ago
    The above code works well, but it seems that it doesn't follow any certain order, in my folder, each excel file ( before merging ) has 1 sheet with same name,for exp: book1 contains sheet named A, book2 also contains sheet named A ,....then after merging by VBA, the combined workbook contains sheets named A, A(1), A(2), A(3),...in random order, A(1) actually doesn't belongs to initial book1. So it's hard for me to identify, I can't know each sheet originally belongs to which file. The result I expect is that all sheets will be combine in name order of original separated file

    Perhaps i can't explain my point well, if someone has same situation or can understand me, then pls kindly upgrade vba code that can combine sheets in order of alphabet name or ascending number ?
  • To post as a guest, your comment is unpublished.
    wooly · 2 years ago
    I'm new to Excel and am struggling to identify what I need to personalise in this code to make it work. Obviously "path" but is that it? as I'm getting errors on the two "set copyrng" and "set dest" row.

    None of these worked for me

    I finally got this one to work. FYI I am using 2010

    'Description: Combines all files in a folder to a master file.
    Sub MergeFiles()
    Dim path As String, ThisWB As String, lngFilecounter As Long
    Dim wbDest As Workbook, shtDest As Worksheet, ws As Worksheet
    Dim Filename As String, Wkb As Workbook
    Dim CopyRng As Range, Dest As Range
    Dim RowofCopySheet As Integer

    RowofCopySheet = 2 ' Row to start on in the sheets you are copying from

    ThisWB = ActiveWorkbook. Name

    path = "mypath....." ' Dont't forget to change this

    Application.Ena bleEvents = False
    Application.Scr eenUpdating = False

    Set shtDest = ActiveWorkbook. Sheets(1)
    Filename = Dir(path & "\*.xls", vbNormal)
    If Len(Filename) = 0 Then Exit Sub
    Do Until Filename = vbNullString
    If Not Filename = ThisWB Then
    Set Wkb = Workbooks.Open( Filename:=path & "\" & Filename)
    Set CopyRng = Wkb.Sheets(1).R ange(Cells(Rowo fCopySheet, 1), Cells(ActiveShe et.UsedRange.Ro ws.Count, ActiveSheet.Use dRange.Columns. Count))
    Set Dest = shtDest.Range(" A" & shtDest.UsedRan ge.SpecialCells (xlCellTypeLast Cell).Row + 1)
    CopyRng.Copy Dest
    Wkb.Close False
    End If

    Filename = Dir()
    Loop

    Range("A1").Sel ect

    Application.Ena bleEvents = True
    Application.Scr eenUpdating = True

    MsgBox "Done!"
    End Sub