How Can I Combine/Merge Multiple Excel Files Into a Single File Horizontally?

[njpamdnc]

Hello...My name is Robert, and I am already familiar with using the Windows command prompt to execute "copy *.xls combine.xls" in order to VERTICALLY join multiple Excel files. However, I am seeking a Windows command prompt function or an Excel macro for HORIZONTALLY merging the workbooks into a single workbook. Furthermore, I am not interested in using Power Query, Linux, Unix, etc. to accomplish this task due to the fact those programs are unfamiliar to me. Any assistance you can provide will be greatly appreciated.

 

[John_w]

See if this macro does what you want. Change the code where indicated.

Public Sub Import_Workbooks_To_Columns()
    
    Dim fileSpec As String, folder As String, fileName As String
    Dim destCell As Range
    Dim wb As Workbook
    
    fileSpec = "C:\folder\path\*.xls"     'CHANGE -location of workbooks and wildcard file spec.
    
    With ActiveSheet
        Set destCell = .Range("A1")
    End With
    
    Application.ScreenUpdating = False
    
    folder = Left(fileSpec, InStrRev(fileSpec, "\"))
    fileName = Dir(fileSpec)
    While fileName <> vbNullString
        Set wb = Workbooks.Open(folder & fileName)
        wb.Worksheets(1).UsedRange.Copy destCell
        wb.Close False
        Set destCell = destCell.Worksheet.Cells(1, destCell.Worksheet.Columns.Count).End(xlToLeft).Offset(, 1)
        fileName = Dir
    Wend

    Application.ScreenUpdating = True
    
    MsgBox "Done"

End Sub