处理多个Microsoft Excel 文件(Microsoft Excel Files)时，有时您需要将 Excel 文件和表格合并(merge Excel Files and Sheets)到一个新的或现有的Excel文件中，或者将多个 Excel 文件合并到一个文件中。虽然您始终可以将粘贴数据从一张纸复制到另一张纸或从一个文件复制到另一张纸，但是当它们太多时会很乏味。在这篇文章中，我们将分享如何使用内置函数合并它们。
如何合并 Excel 文件和表格
在开始合并文件和工作表之前，请做好计划。虽然您以后总是可以重新排列工作表，但您计划得越好，合并后花费在排序上的时间就越少。
- 将表格合并(Merge Sheets)到新文件或现有文件
- 合并多个 Excel 文件
虽然我们将在此处使用的功能将为您提供移动工作表的选项，但我建议您进行复制。如果您再次需要原始文件，您可以选择。
将表格合并(Merge Sheets)到新文件或现有文件
在我们开始合并之前，请确保您已打开所有Excel文件。只有当它们打开时，Excel合并功能才能选择它作为目标。如果您打算将工作表从多个文件移动到一个新的 excel 文件中，您也可以这样做。
- 打开源excel文件，然后切换到要复制的工作表
- 单击(Click)主页(Home)选项卡>> Cells 部分> Format > Move或复制工作表
- 它将打开一个弹出窗口，您可以从中选择现有的 excel 文件或在运行时创建一个新文件
- 当您选择一个新文件选项(choose a new file option)时，它会立即创建一个新文件但不会保存它。
- 当您选择现有文件(choose an existing file)时，您可以选择插入工作表的位置，即在现有工作表之前或之后或所有工作表的末尾
- 确保选中复选框 -创建副本。(Create a copy.)它将确保您现有的工作表保留在源书中。
您还可以移动到现有文件中，如果工作表太多，这很方便，并且您需要将工作表插入它们之间的某个地方，或者将其移动到最后。
如果要将多个工作表移动到另一个Excel文件，则在使用“移动或复制工作表”之前，使用(Move or copy sheet,”)Ctrl或Shift选择工作表。虽然Shift将允许您选择相邻的工作表或工作表范围，但Ctrl将允许您选择单个工作表。其余步骤相同。您可以使用它来手动合并 excel 文件。
合并多个 Excel 文件
合并Excel文件是一件棘手的事情，为此，我们将使用ExtendOffice中的(ExtendOffice)VBA代码。它将允许您组合文件夹中可用的多个文件或工作簿。
- 创建一个新的Excel电子表格，然后按ALT + F11打开开发人员(Developer)部分
- 单击(Click)插入(Insert)菜单，然后单击模块(Module)
- 粘贴下面提到的代码。将模块(Module)命名为MergeExcel
Sub MergeExcel()
Path = "D:\ExcelFiles\"
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
接下来，按 Alt + F8 打开 宏(Macro)对话框。它将显示工作表中的所有宏(Macros)。选择 MergeExcel 并单击 运行(Run)。系统将提示您保存文件，请务必这样做。完成后，来自不同 excel 文件的所有工作表都将在您运行Macro的 excel 文件中可用。ExtendOffice 网站(ExtendOffice Website)有很多这样的宏，我强烈建议您访问extendoffice.com。
也就是说，第一次尝试不会是一次顺利的体验。请放心，它不会删除任何文件，但我建议保留文件的备份。尝试一些随机数据，并检查您是否使用宏获得了所需的结果。
我们希望教程易于理解，并且您可以合并Excel 文件(Excel Files)和表格(Sheets)。
How to merge Excel Files and Sheets
When working with multiple Microsoft Excel Files, at times, you need to merge Excel Files and Sheets to a new or existing Excel file or merge multiple excel files into one file. While you can always copy paste data from one sheet to another or from one file to another, it’s tedious when there are too many of them. In this post, we will share how you can merge them using the inbuilt function.
How to Merge Excel Files & Sheets
Before you start merging files and sheets, plan it well. While you can always rearrange sheets later, the better you plan, the less amount of hours you will spend on sorting them after merging.
- Merge Sheets to a new or existing file
- Merge Multiple Excel Files
While the functions we will use here will give you the option to move sheets, I will recommend copying. If you ever need the original file again, you will have the choice.
Merge Sheets to a new or existing file
Before we start merging, make sure you have all the Excel files open. Only when they are open, Excel merge function can choose that as a destination. If you plan to move sheets from multiple files into a new excel file, you can do that as well.
- Open the source excel file, and switch to the sheet you wish to copy
- Click on Home tab > Cells section > Format > Move or copy sheet
- It will open a popup from where you can select existing excel file or create a new file on the run
- When you choose a new file option, it will instantly create a new file but will not save it.
- When you choose an existing file, you have the option to select where the sheet will be inserted, i.e., before or after existing sheets or end of all sheets
- Make sure to select the checkbox— Create a copy. It will make sure your existing sheets remain in the sourcebook.
You can also move into an existing file, which is handy if there are too many sheets, and you need to insert the sheet in between them, somewhere, or move it to the end.
If you want to move multiple sheets to another Excel file, then before using “Move or copy sheet,” select the sheets using Ctrl or Shift. While Shift will allow you to select adjacent sheets or range of sheets, Ctrl will let you choose the individual sheets. The rest of the steps are the same. You can use this to manually merging excel files.
Merge Multiple Excel Files
Merging Excel files is a tricky thing, and for that, we will use a VBA code from ExtendOffice. It will allow you to combine multiple files or workbooks available in a folder.
- Create a new Excel spreadsheet, and press ALT + F11 to open Developer section
- Click on Insert menu, and then Module
- Paste the code mentioned below. Name the Module as MergeExcel
Sub MergeExcel()
Path = "D:\ExcelFiles\"
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
Next, press Alt + F8 to open the Macro dialog. It will reveal all the Macros in the sheet. Select MergeExcel and click Run. You will be prompted to save files, make sure to do so. Done that, all the sheets from different excel files will become available into the excel file where you ran the Macro. ExtendOffice Website has many such macros, and I would strongly suggest to visit them at extendoffice.com.
That said, it is not going to be a smooth experience at the first try. Stay assured that it will not delete any files, but I would suggest keeping a backup of the files. Experiment with some random data, and check if you are getting the desired result using the macro.
We hope the tutorial was easy to understand, and you can merge Excel Files and Sheets.
