Easily Consolidate Excel files using Excel VBA

Ever stumbled upon the need to compile multiple Excel files? Maybe you've got monthly reports, datasets from different departments, or just a clutter of related files. We've all been there, and manually copying data can be tedious. But fret not! With the power of VBA (Visual Basic for Applications), you can automate this in no time. And today, I'm going to show you exactly how!

What We Aim to Achieve:

Imagine you have a folder filled with Excel files. All these files follow a similar format. Wouldn't it be convenient if you could merge all of them into a single file with the click of a button? Yes, you guessed it right - we're turning that dream into reality!

The Magic Wand - VBA Code:

For those who haven't dabbled in VBA before, it's a programming language built into Excel that helps you automate tasks. Here's the simple VBA code that will do the merging for us:

Sub ConsolidateWorkbooks()

    Dim FolderPath As String
    Dim Filename As String
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim CurrentWS As Worksheet
    Dim LastRow As Long
    
    ' Turn off screen updating for performance
    Application.ScreenUpdating = False
    
    ' Get the folder path from B2 cell
    FolderPath = ThisWorkbook.Sheets("Sheet1").Range("B2").Value

    ' Check if folder path ends with backslash. If not, add it.
    If Right(FolderPath, 1) <> "\" Then
        FolderPath = FolderPath & "\"
    End If

    ' Get the name of the folder for the new sheet
    Dim FolderName As String
    FolderName = Mid(FolderPath, InStrRev(FolderPath, "\") + 1)
    FolderName = Left(FolderName, Len(FolderName) - 1)
    
    ' Add a new worksheet with the folder name
    Set CurrentWS = ThisWorkbook.Sheets.Add
    CurrentWS.Name = FolderName
    
    ' Set the initial filename
    Filename = Dir(FolderPath & "*.xls*")
    
    Do While Filename <> ""
        ' Open the file
        Set wb = Workbooks.Open(FolderPath & Filename)
        
        ' Copy data from opened workbook to the main workbook
        For Each ws In wb.Worksheets
            LastRow = CurrentWS.Cells(CurrentWS.Rows.Count, "A").End(xlUp).Row + 1
            ws.UsedRange.Copy CurrentWS.Cells(LastRow, 1)
        Next ws
        
        ' Close the opened workbook
        wb.Close SaveChanges:=False
        
        ' Get the next filename
        Filename = Dir
    Loop
    
    ' Turn screen updating back on
    Application.ScreenUpdating = True

    MsgBox "Data Consolidated Successfully!", vbInformation

End Sub

---

Code

Sub ConsolidateWorkbooks()

This declares the beginning of the subroutine named "ConsolidateWorkbooks."

Explanation:

- The subroutine will merge data from all the Excel files (`*.xls*`) in a folder into a new worksheet in the active workbook.

---

Code

Dim FolderPath As String
Dim Filename As String
Dim ws As Worksheet
Dim wb As Workbook
Dim CurrentWS As Worksheet
Dim LastRow As Long

Explanation:

- Variables are declared to store various values and references used in the script.
- `FolderPath`: Path of the folder containing the Excel files to consolidate.
- `Filename`: Name of each Excel file in the folder.
- `ws`: Represents each individual worksheet in the opened workbook.
- `wb`: Represents the workbook currently being processed.
- `CurrentWS`: Represents the newly created worksheet in the main workbook where data is consolidated.
- `LastRow`: Represents the last used row in `CurrentWS` for data copying purposes.

---

Code

Application.ScreenUpdating = False

Explanation:

- Screen updating is turned off to improve performance during the consolidation process.

---

Code

FolderPath = ThisWorkbook.Sheets("Sheet1").Range("B2").Value

Explanation:

- The path of the folder containing Excel files is read from cell B2 of "Sheet1" in the active workbook.

---

Code

If Right(FolderPath, 1) <> "\" Then
FolderPath = FolderPath & "\"
End If

Explanation:

- Checks if the `FolderPath` ends with a backslash (`\`). If not, one is added to ensure a valid path.

---

Code

Dim FolderName As String
FolderName = Mid(FolderPath, InStrRev(FolderPath, "\") + 1)
FolderName = Left(FolderName, Len(FolderName) - 1)

Explanation:

- Extracts the folder's name from the full path. This name will be used as the name of the new worksheet where data is consolidated.

---

Code

Set CurrentWS = ThisWorkbook.Sheets.Add
CurrentWS.Name = FolderName

Explanation:

- A new worksheet is added to the main workbook, and it's named after the folder.

---

Code

Filename = Dir(FolderPath & "*.xls*")

Explanation:

- Starts the process of looping through all Excel files (`*.xls*`, which includes both `.xls` and `.xlsx` formats) in the folder.

---

Code

Do While Filename <> ""

Explanation:

- Begins a loop that will process each Excel file in the folder until there are no more left.

---

Code

Set wb = Workbooks.Open(FolderPath & Filename)

Explanation:

- Opens the current Excel file for processing.

---

Code

For Each ws In wb.Worksheets
LastRow = CurrentWS.Cells(CurrentWS.Rows.Count, "A").End(xlUp).Row + 1
ws.UsedRange.Copy CurrentWS.Cells(LastRow, 1)
Next ws

Explanation:

- Loops through each worksheet in the currently opened workbook.
- Determines the last used row in the `CurrentWS`.
- Copies the used range from the source worksheet to the `CurrentWS`, starting after the last used row.

---

Code

wb.Close SaveChanges:=False

Explanation:

- Closes the currently opened workbook without saving any changes.

---

Code

Filename = Dir

Explanation:

- Gets the next Excel file in the folder for processing.

---

Code

Loop

Explanation:

- Ends the loop that processes each Excel file.

---

Code

Application.ScreenUpdating = True

Explanation:

- Screen updating is turned back on to reflect all changes made during the consolidation process.

---

Code

MsgBox "Data Consolidated Successfully!", vbInformation

Explanation:

- A message box is displayed to inform the user that the data has been successfully consolidated.

---

Code

End Sub

Explanation:

- Marks the end of the subroutine.

In summary, this VBA subroutine consolidates data from all Excel files in a specified folder into a new worksheet in the active workbook. The user provides the folder path in cell B2 of "Sheet1."

Your feedback and suggestion is very valuable for us.
So please comment your thoughts and request code below