跳到主要內容

如何在Excel中將多個工作簿合併為一個主工作簿?

當您必須在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 鍵來運行代碼。 然後,將某個文件夾中工作簿的所有工作表或指定工作表一次組合到一個主工作簿中。


輕鬆將多個工作簿或指定的工作簿表合併為一個工作簿

幸運的是, 結合 的工作簿實用程序 Excel的Kutools 使將多個工作簿合併為一個更加容易。 讓我們看看如何在組合多個工作簿時使此功能發揮作用。

申請前 Excel的Kutools首先下載並安裝.

1.創建一個新的工作簿,然後單擊 Kutools 加 > 結合。 然後會彈出一個對話框,提醒您所有組合的工作簿都應保存,並且該功能不能應用於受保護的工作簿,請單擊 OK 按鈕。

2。 在裡面 合併工作表 嚮導,選擇 將工作簿中的多個工作表合併到一個工作簿中 選項,然後單擊 下一頁 按鈕。 看截圖:

3。 在裡面 合併工作表-第2步,共3步 對話框中,單擊 加入 > 文件 or 要添加Excel文件,您將合併為一個。 添加Excel文件後,點擊 按鈕,然後選擇一個文件夾來保存主工作簿。 看截圖:

現在,所有工作簿都合併為一個。

與以上兩種方法相比, Excel的Kutools 具有以下優點:

  • 1)所有工作簿和工作表都在對話框中列出;
  • 2)對於要從合併中排除的工作表,只需取消選中它;
  • 3)空白工作表被自動排除;
  • 4)合併後,原始文件名將作為工作表名稱的前綴添加;
  • 有關此功能的更多功能, 請訪問這裡.

  如果您想免費試用(30天)此實用程序, 請點擊下載,然後按照上述步驟進行操作。


Kutools for Excel- 幫助您始終提前完成工作,有更多時間享受生活
您是否經常發現自己正在趕上工作,缺乏時間為自己和家人度過?  Excel的Kutools 可以幫你處理 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
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations