Combine files from multiple workbooks into one

[mark91345]

Using Excel 2016, I have 20 .xlsx files which I would like to combine into one workbook. Note that each files has only one worksheet, so it should be an easy merge (I don't care about the name of the worksheets or anything like that).

I have tried copying some VBA codes from other posts, but I am always getting some kind of error (perhaps they were for older versions of Excel, I don't know).

I am clueless about VBA, but I see I need it desperately.

 

[JLGWhiz]

If the workbook that hosts the code is in the same directory and that workbook and the 20 you want to merge are the only files in that directory then you can use this code. This assumes you want all but the header row of each sheet in the 20 workbooks.

Sub t()
Dim wb As Workbook, sh As Worksheet, fPath As String, fName As String
Set sh = ThisWorkbook.Sheets(1)
fPath = ThisWorkbook.Path
If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
fName = Dir(fPath & "*.xl*")
    Do While fName <> ""
        If fName <> ThisWorkbook.Name Then
            Set wb = Workbooks.Open(fPath & fName)
            wb.Sheets(1).UsedRange.Offset(1).Copy sh.Cells(Rows.Count, 1).End(xlUp)(2)
            wb.Close False
        End If
        fName = Dir
    Loop
End Sub

The code is to be pasted into code module1 of the host workbook. The host workbook need to be saved as a macro enabled (.xlsm) workbook. To access the code module, press Alt + F11 on the keyboard. The vb editor should open and you can look at the top margin of the editor window to see which code module it opened to. You want to se 'filename-[Module1(Code)]' If the code pane is dark, clidk Insert on the editor tool bar and then click 'Module'. When the code pane is bright, paste the code into it, close the editor and save the workbook as a macro enabled workbook. To run the code from the Excel window, press Alt + F8, double click the macro name, or click the name once and then click the 'Run' button in the dialog box.

If the host workbook is in a different directory, then you will need to use the directory path for the 20 workbooks insteat of ThisWorkbook.Path. Example:

fPath = "C:\User\Documents\xl"

Again, that is only an example of a path, you need to get the actual path of the 20 workbooks if they are in a different directory.

 

[archilles89]

Hi thank you for this! Sorry to bring up an old thread.

I have a similar problem. In my case, I would like to have my 20 Excel files be combined into 1 single sheet (every sheet is added at the bottom of the master sheet), resulting into only 1 sheet for all 20 Excel files. Is this possible?