Copy all the columns from multiple Excel files into a single sheet

To copy all the columns from multiple Excel files into a single sheet (Sheet1) of your merged workbook, with each file's content placed in consecutive columns, you can adjust the VBA code as follows:

VBA Code

Sub MergeExcelFilesIntoOneSheet()
    Dim Path As String
    Dim Filename As String
    Dim wbSource As Workbook
    Dim ws As Worksheet
    Dim targetSheet As Worksheet
    Dim lastCol As Long
    Dim lastRow As Long
    Dim copyRange As Range
    Dim targetCol As Long
    Dim isFirstFile As Boolean

    ' Set the path to the folder containing the Excel files
    Path = "C:\Your\Folder\Path\Here\"  ' Update this to your folder path

    ' Set the initial target column to 1 (Column A)
    targetCol = 1

    ' Set the target sheet in the current workbook (Sheet1)
    Set targetSheet = ThisWorkbook.Sheets("Sheet1")
    
    ' Get the first Excel file from the directory
    Filename = Dir(Path & "*.xlsx")
    
    ' Loop through all Excel files in the folder
    Do While Filename <> ""
        ' Open the current Excel file
        Set wbSource = Workbooks.Open(Path & Filename, ReadOnly:=True)
        
        ' Loop through each sheet in the opened workbook
        For Each ws In wbSource.Sheets
            ' Find the last row and column in the source sheet
            lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
            lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
            
            ' Set the range to be copied
            Set copyRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))
            
            ' Copy the range to the target sheet, starting at the target column
            copyRange.Copy Destination:=targetSheet.Cells(1, targetCol)
            
            ' Update the target column for the next sheet/file
            targetCol = targetCol + copyRange.Columns.Count
        Next ws
        
        ' Close the source workbook without saving
        wbSource.Close False
        
        ' Move to the next file
        Filename = Dir()
    Loop
    
    MsgBox "All sheets have been merged into Sheet1."
End Sub

How It Works

1. Target Column:

   • The targetCol variable tracks the column in Sheet1 where the next data will be pasted. It starts from column 1 (A) and is incremented by the number of columns in each sheet that is copied.

2. Copying Data:

   • For each sheet in the source workbooks, the code copies the entire content of the sheet and pastes it into Sheet1, starting at the current targetCol.

3. Appending Data:

   • After each sheet's data is copied, targetCol is updated to the column immediately after the last pasted column, so that the next sheet's data is appended horizontally.

4. Merging Multiple Files:

   • The code loops through all the files in the specified folder, copying each sheet's content into the same Sheet1.

Steps to Use the Code

1. Insert the VBA Code:

   • Open the VBA editor in Excel, insert a module, and paste the updated code.

2. Set the Path:

   • Update the Path variable to point to the folder where your Excel files are stored.

3. Ensure Sheet1 Exists:

   • Make sure that Sheet1 exists in your workbook before running the macro.

4. Run the Macro:

   • Press F5 or go to Run > Run Sub/UserForm to execute the macro.

5. Save the Workbook:

   • After the macro completes, save your workbook. All the data from the files will be merged into columns in Sheet1.

This will result in a single sheet (Sheet1) containing all the data from the sheets in the specified Excel files, with each file's data placed consecutively in columns.