VBA - How to merge multiple Excel files into one but different Sheets

Let us assume, you receive sales data (or any data) from different regions or zones in multiple Excel files, via email or any other source. Now, you want to merge all the Excel files into one but in different sheets. Here I'll show you how to combine multiple excel files into one but multiple (or different) worksheet using macro.

See the below image and you’ll understand what I am trying to explain here.

Merge multiple Excel files in single workbook but different sheets using VBA

Here’s what I am doing

I have three files, east.xlsx, west.xlsx and north.xlsx in a folder. The macro will open the files, extract data from each file and write the data in different sheets in the master file (the Excel file in which I’ll merge all the data).

Write the macro in the master file.

The VBA Code (Macro)

Option Explicit

Private Sub CommandButton1_Click()
    mergeData
End Sub

Sub mergeData()
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    
    ' Our FileSystem Objects.
    Dim objFs As Object
    Dim objFolder As Object
    Dim file As Object
    
    Set objFs = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFs.GetFolder("D:\sample")       ' The path of the source files.
    
    Dim iCnt As Integer     ' Just a counter.
    iCnt = 1
    
    ' Loop through all the files in the folder.
    For Each file In objFolder.Files
    
        Dim objSrc As Workbook     ' The source.
        Set objSrc = Workbooks.Open(file.Path, True, True)
        
        Dim iTotalRows As Integer       ' The total rows used in the source file.
        iTotalRows = objSrc.Worksheets("sheet1").UsedRange.Rows.Count
        
        Dim iTotalCols As Integer       ' Now, get the total columns in the source.
        iTotalCols = objSrc.Worksheets("sheet1").UsedRange.Columns.Count
        
        Dim iRows, iCols As Integer
        
        ' Read data from source and copy in the master file.
        For iRows = 1 To iTotalRows
            For iCols = 1 To iTotalCols
                Application.Workbooks(1).ActiveSheet.Cells(iRows, iCols) = _
                        objSrc.Worksheets("Sheet1").Cells(iRows, iCols)
                            ' Note: It will read data in "Sheet1" of the source file.
            Next iCols
        Next iRows
        
        iRows = 0
        
        ' Get the name of the file (I'll name the active sheet with the filename).
        Dim sSheetName As String
        sSheetName = Replace(objSrc.Name, ".xlsx", "")          ' I am assuming the files are .xlsx files.
        
        ' Close the source file (the file from which its copying the data).
        objSrc.Close False
        Set objSrc = Nothing
        
        With ActiveWorkbook
            .ActiveSheet.Name = sSheetName           ' Rename the sheet.
            iCnt = iCnt + 1
            
            If iCnt > .Worksheets.Count Then
                ' Create or add a new sheet after the last sheet.
                .Sheets.Add After:=.Worksheets(.Worksheets.Count)
            End If
            
            .Worksheets(iCnt).Activate      ' Go to the next sheet.
        End With

    Next
    
ErrHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

The mergeData procedure is called upon button click. So, I have added an ActiveX button control to the main file or the master file.

I am using the FileSystemObject to get access to the folder and the files in it.

Next, it loops through each file to read and extract data from it and write the data as it is in the master file’s active sheet.

For Each file In objFolder.Files
…
Next

As I have said, I’ll write a particular file's data in a single sheet. See this line here …

Application.Workbooks(1).ActiveSheet.Cells(iRows, iCols) = objSrc.Worksheets("Sheet1").Cells(iRows, iCols)

It's the "ActiveSheet" that I am writing the data into. Therefore, its important that the focus is always on the worksheet in which you’ll write the data. To set focus on a worksheet, you can use the Activate method like this…

ActiveWorkbook.Worksheets(iCnt).Activate ' Go to the next sheet.

In-addition, the macro will rename the worksheets with the file name it extracts data. So, it will be easy to keep a track on the data that it will merge.

ActiveWorkbook.ActiveSheet.Name = sSheetName ' Rename the sheet.

Create New Worksheet dynamically

By default, the master workbook may only have one or two sheets (worksheets) and the source folder might have more Excel files. In this case, we want the macro to create new worksheets dynamically. I have also added that piece of the code in the macro.

If iCnt > .Worksheets.Count Then
    ' Create or add a new sheet after the last sheet.
    .Sheets.Add After:=.Worksheets(.Worksheets.Count)
End If

Now, you can add more files in the source folder and this Marco will add a new worksheet accordingly and merge the data in a new sheet.

2nd Method - Add a Popup (File Dialog Box) to Choose Folder Path

In the above example, I have hard-coded the path of the folder.

Now, let's make it a little more dynamic and useful by having a "pop-up" to choose the folder path, instead of hard coding it. In such case, you can open a File Dialog box upon button click and select a folder of your choice.

Note: Most of the code is similar to the first macro above. In this example however, I have added a fuction named chooseFolder() of type string, which will open a File Dialog box, and will the return folder name and path that you'll choose.

Sub mergeData()
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    
    ' Our FileSystem Objects.
    Dim objFs As Object
    Dim objFolder As Object
    Dim file As Object
    
    'Show a pop up to select a folder.
    Dim sPath As String
    sPath = chooseFolder()
    
    Set objFs = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFs.GetFolder(sPath)       ' The folder path.
    
    Dim iCnt As Integer
    iCnt = 1
    
    ' Loop through all the files in the folder.
    For Each file In objFolder.Files
    
        Dim objSrc As Workbook      ' The source.
        Set objSrc = Workbooks.Open(file.Path, True, True)
        
        Dim iTotalRows As Integer   ' The total used range in the source file.
        iTotalRows = objSrc.Worksheets("sheet1").UsedRange.Rows.Count
        
        Dim iTotalCols As Integer   ' Now, get the total columns in the source.
        iTotalCols = objSrc.Worksheets("sheet1").UsedRange.Columns.Count
        
        Dim iRows, iCols As Integer
        
        ' Read data from source and copy in the master file.
        For iRows = 1 To iTotalRows
            For iCols = 1 To iTotalCols
                Application.Workbooks(1).ActiveSheet.Cells(iRows, iCols) = _
                    objSrc.Worksheets("Sheet1").Cells(iRows, iCols)
                        ' Note: It will read data in "Sheet1" of the source file.
            Next iCols
        Next iRows
        
        iRows = 0
        
        ' Get the name of the file (I'll name the active sheet with the filename).
        Dim sSheetName As String
        sSheetName = Replace(objSrc.Name, ".xlsx", "")          ' Assuming the files are .xlsx files.
        
        ' Close the source file (the file from which its copying the data).
        objSrc.Close False
        Set objSrc = Nothing
        
        With ActiveWorkbook
            .ActiveSheet.Name = sSheetName           ' Rename the sheet.
            iCnt = iCnt + 1
            
            If iCnt > .Worksheets.Count Then
                ' Create or add a new sheet after the last sheet.
                .Sheets.Add After:=.Worksheets(.Worksheets.Count)
            End If
            
            .Worksheets(iCnt).Activate      ' Go to the next sheet.
        End With
    Next
ErrHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

' Open file dialog box to select a folder.
Function chooseFolder() As String
    Dim fd As Office.FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    
    With fd
        .Filters.Clear
        .Title = "Select an Excel File"
        .Filters.Add "Excel Files", "*.xlsx?", 1
        .AllowMultiSelect = True
        
        Dim sPath As String
    
        If .Show = True Then
            chooseFolder = fd.InitialFileName  ' Get the folder path.
        End If
    End With
End Function

Conclusion

There may be other methods to merge Excel files in a single workbook. However, this VBA macro (above) will do what I intended to do. It will quickly merge data extracted from multiple Excel files in different sheets in the master Excel file.

This method is simple and clean. All you have to do is mention the path of the files clearly and the macro will do the rest. You’ll also learn how to create worksheets (or sheets) in your Excel file dynamically in the above example.

And most importantly, the above code will work with older versions of Excel along with the new versions.

← PreviousNext →