שפּיץ: אנדערע שפּראַכן זענען גוגל-איבערגעזעצט. איר קענען באַזוכן דעם English ווערסיע פון ​​דעם לינק.
צייכן אריין
x
or
x
x
רעגיסטרירן
x

or

ווי צו פאַרבינדן קייפל וואָרקבאָאָקס אין איין בעל וואָרקבאָאָק אין עקססעל?

Have you ever been stuck when you have to combine multiple workbooks into a master workbook in Excel? The most terrible thing is that the workbooks you need to combine contain multiple worksheets. And how to combine only the specified worksheets of multiple workbooks into one workbook? This tutorial demonstrates several useful methods to help you solve the problem steps by steps.


Easily combine multiple workbooks into one in Excel:

די פאַרבינדן workbooks utility of קוטאָאָלס פֿאַר עקססעל can help you to easily combine multiple Excel files into one. Besides, it allows you to only combine the specified worksheets of certain Excel files to a master workbook easily. אָפּלאָדירן די גאַנץ שטריך קסנומקס-טאָג פֿרייַ שטעג איצט!

Combine multiple workbooks to one workbook with Move or Copy function

If there are just a couple of workbooks need to be combined, you can use the Move or Copy command to manually move or copy worksheets from the original workbook to the master workbook.

1. Open the workbooks which you will merge into a master workbook.

2. Select the worksheets in the original workbook that you will move or copy to the master workbook.

הערות:

1). You can select multiple non-adjacent worksheets with holding the קטרל key and clicking the sheet tabs one by one.

2). For selecting multiple adjacent worksheets, please click on the first sheet tab, hold the יבעררוק key, and then click the last sheet tab to select them all.

3). You can right click on any sheet tab, click on אויסקלייַבן אַלע שיץ from the context menu to select all worksheets in the workbook at the same time.

3. After selecting the needed worksheets, right click the sheet tab, and then click מאַך אָדער קאָפּי פון די קאָנטעקסט מעניו. זען סקרעענשאָט:

4. דערנאך די מאַך אָדער קאָפּי dialog pops up, in the צו באשטעלן drop-down, select the master workbook you will move or copy worksheets into. Select move to end in the איידער בויגן קעסטל, טשעק די שאַפֿן אַ קאָפּיע קעסטל, און לעסאָף גיט די OK קנעפּל.

Then you can see worksheets in two workbooks combined into one. Please repeat the above steps to move worksheets from other workbooks into the master workbook.

Office Tab - a real time-saver add-in!

Display all opened documents in a single window to help quickly finding a needed document at a glance.

Full feature free trial 45-day. Compatible with every version of Excel, Word and PowerPoint.

KNOW MORE ABOUT THE ADD-IN >

Combine multiple workbooks or specified sheets of workbooks to a master workbook with VBA

If there are multiple workbooks need to be merged into one, you can apply the following VBA codes to quickly achieve it. Please do as follows.

1. Put all workbooks that you want to combine into one under the same directory.

2. Launch an Excel file (this workbook will be the master workbook).

3. דרוק די אַלט + פקסנומקס שליסלען צו עפענען די מיקראָסאָפט וויסואַל בייסיק פֿאַר פּראָגראַמען פענצטער. אין די מיקראָסאָפט וויסואַל בייסיק פֿאַר פּראָגראַמען פֿענצטער, גיט אַרייַנלייגן > מאָדולעס, דעמאָלט קאָפּיע אונטער וובאַ קאָד אין די מאָדולע פֿענצטער.

VBA code 1: Merge multiple Excel workbooks into one

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. The above VBA code will keep the sheet names of the original workbooks after merging.

2. If you want to distinguish which worksheets in the master workbook came from where after merging, please apply the below VBA code 2.

3. If you just want to combine specified worksheets of the workbooks into a master workbook, the below VBA code 3 can help.

In VBA codes, “C:\Users\DT168\Desktop\KTE\” is the folder path. In the VBA code 3, "Sheet1,Sheet3" is the specified worksheets of the workbooks you will combine to a master workbook. You can change them based on your needs.

VBA code 2: Merge Workbooks into one (each worksheet will be named with prefix of its original file name):

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 code 3: Merge specified worksheets of workbooks into a master workbook:

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 key to run the code. Then all worksheets or specified worksheets of the workbooks in the certain folder are combined to a master workbook at once.

Easily combine multiple workbooks or specified sheets of workbooks to one workbook

קוטאָאָלס פֿאַר עקססעל

עס איז אַ האַנטיק עקססעל לייג-אין וואָס זאַמלען מער ווי קסנומקס אַוואַנסירטע פֿעיִקייטן צו פאַרפּאָשעטערן דיין אַרבעט אין עקססעל אַפּעריישאַנז. מיט אים, איר קענען לייכט:

  • פאַרבינדן מולטי-וואָרקבאָאָקס;
  • ציילן / סומע סעלז דורך קאָלירן;
  • שפּאַלטן סעלז;
  • פּעקל באַזייַטיקן ספּייסיז;
  • מער פֿעיִקייטן ...

FREE DOWNLOAD NOW!

גליק, די פאַרבינדן workbook utility of קוטאָאָלס פֿאַר עקססעל makes it much easier to merge multiple workbooks into one. Let's see how to get this function work in combining multiple workbooks.

1. Create a new workbook and click Kutools More > פאַרבינדן. דערנאך אַ דיאַלאָג פּאָפּס צו דערמאָנען איר אַז אַלע קאַמביינד וואָרקבאָאָקס זאָל זיין געזונט און די שטריך קענען ניט זיין געווענדט צו פּראָטעקטעד וואָרקבאָאָקס, ביטע גיט די OK קנעפּל.

2. אין די Combine Worksheets מאַזעק, אויסקלייַבן קאַמביינד קייפל ווערקשיץ פון וואָרקבאָאָקס אין איין וואָרקבאָאָק אָפּציע, און דעמאָלט גיט די ווייַטער קנעפּל. זען סקרעענשאָט:

3. אין די פאַרבינדן וואָרקשעעץ - שריט קסנומקס פון קסנומקס דיאַלאָג קעסטל, גיט די צוגעבן > טעקע or טעקע to add the Excel files you will merge into one. After adding the Excel files, click the ענדיקן button and choose a folder to save the master workbook. See screenshot:

Now all workbooks are merged into one.

Compared with the above two methods, קוטאָאָלס פֿאַר עקססעל האט די פאלגענדע אַדוואַנטידזשיז:

  • 1) All workbooks and worksheets are listed in the dialog box;
  • 2) For the worksheets you want to exclude from merging, just uncheck it;
  • 3) Blank worksheets are excluded automatically;
  • 4) The original file name will be added as prefix to the sheet name after merging;
  • For more functions of this feature, ביטע באַזוכן דאָ.

Does this Combine workbooks utility help? Let's download and try the full feature free trial 60-day!

רעקאַמענדיד פּראָדוקטיוויטי מכשירים פֿאַר מיקראָסאָפט אָפפיסע

אָפפיסע טאַב - טאַבבעד בראַוזינג, עדיטינג, און מאַנאַגינג אין עקססעל / וואָרט / פּאָווערפּאָינט:

אָפיס טאַב ברענגט די טאַבבעד צובינד ווי געזען אין וועב בראַוזערז אַזאַ ווי Google קראָום, Internet Explorer נייַ ווערסיעס און פירעפאָקס צו Microsoft Excel / Word / PowerPoint. עס וועט זיין אַ צייַט שפּאָרן געצייַג און יראַפּלייסאַבאַל אין דיין אַרבעט. זען אונטער דעמאָ:

ot1

קוטאָאָלס פֿאַר עקססעל - קאַמביינז מער ווי קסנומקס אַוואַנסירטע פאַנגשאַנז און מכשירים פֿאַר מיקראָסאָפט עקססעל:

קוטאָאָלס פֿאַר עקססעל איז אַ האַנטיק עקססעל לייג-אין מיט מער ווי קסנומקס אַוואַנסירטע פֿעיִקייטן צו פאַרפּאָשעטערן פאַרשידן מינים פון קאָמפּליצירט טאַסקס אין אַ ביסל קליקס אין עקססעל. פֿאַר בייַשפּיל:

  • פאַרבינדן קייפל ווערקשיט אָדער וואָרקבאָאָקס אין איין ווערקבוך
  • ציילן און סומע סעלז באזירט אויף הינטערגרונט / שריפֿט קאָליר
  • שפּאַלטן צעל אינהאַלט אין קייפל ראָוז / שפאלטן דורך אָרט / קאָממאַ / דעלימיטאָר
  • פּעקל אַראָפּנעמען לידינג ספּייסאַז אין סעלז
קטע

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.
    Dr. Paul B. · 4 years ago
    That code was great! Thanks so much.
  • To post as a guest, your comment is unpublished.
    Aren · 4 years ago
    I use an app rather than code and recode. Data Amalgamation by pasync
  • To post as a guest, your comment is unpublished.
    khar booza · 4 years ago
    This is very hard, is it possible you can do it for me?
  • To post as a guest, your comment is unpublished.
    Niki Foster · 4 years ago
    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.EnableEvents = False
    Application.ScreenUpdating = 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).Range(Cells(RowofCopySheet, 1), Cells(ActiveSheet.UsedRange.Rows.Count, ActiveSheet.UsedRange.Columns.Count))
    Set Dest = shtDest.Range("A" & shtDest.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1)
    CopyRng.Copy Dest
    Wkb.Close False
    End If

    Filename = Dir()
    Loop

    Range("A1").Select

    Application.EnableEvents = True
    Application.ScreenUpdating = True

    MsgBox "Done!"
    End Sub
    • To post as a guest, your comment is unpublished.
      Mohideen · 2 years ago
      HI , I have multiple Excel File (single sheet) different folder with password protection. i want end of the day combine all data to one Master file. Every time I have to Enter password and open the file and copy paste to master file.. Kindly help me with VBA code for this please.
    • To post as a guest, your comment is unpublished.
      michelle · 4 years ago
      How do I edit this so that the data pulled in always starts on the top row? If I run this code twice, it adds the data to the end of my previous data (from the first run of the macro).
      • To post as a guest, your comment is unpublished.
        orivera · 4 years ago
        Change this line:

        RowofCopySheet = 2

        to

        RowofCopySheet = 1
  • To post as a guest, your comment is unpublished.
    Ginger41 · 4 years ago
    Try this out...I got this from another site but unfortunately I can't remember the lady's name so my apologies for not giving her a mention, my bad"

    Combine multiple WB's in Excel:
    REMEMBER TO CHANGE MyPath = !

    Sub Merge2MultiSheets()
    Dim wbDst As Workbook
    Dim wbSrc As Workbook
    Dim wsSrc As Worksheet
    Dim MyPath As String
    Dim strFilename As String

    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    MyPath = "\\MyPath\etc\etc..."
    Set wbDst = Workbooks.Add(xlWBATWorksheet)
    strFilename = Dir(MyPath & "\*.xls", vbNormal)

    If Len(strFilename) = 0 Then Exit Sub

    Do Until strFilename = ""

    Set wbSrc = Workbooks.Open(Filename:=MyPath & "\" & strFilename)

    Set wsSrc = wbSrc.Worksheets(1)

    wsSrc.Copy After:=wbDst.Worksheets(wbDst.Worksheets.Count)

    wbSrc.Close False

    strFilename = Dir()

    Loop
    wbDst.Worksheets(1).Delete

    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True

    End Sub
    • To post as a guest, your comment is unpublished.
      Ghulam · 1 years ago
      Hi
      I added the code into a module. Named the Excel book Masterfile.

      Where in the code do I add. thank you