Automate Data Consolidation Processes with VBA in Excel

Here is a detailed VBA code to automate the process of consolidating data from multiple Excel sheets or files into one master sheet. This example assumes you are consolidating data from several files or sheets with similar structures, and your goal is to gather and organize the data into a single consolidation sheet.

Objective:

• Open multiple Excel files or sheets.
• Consolidate data from these files/sheets into one master sheet.
• Perform tasks like removing duplicates and applying formatting.

General Steps:

1. Open source files.
2. Loop through each file/sheet to collect the data.
3. Copy the data to a consolidation sheet.
4. Remove duplicates (if needed).
5. Format the consolidation sheet (adding column headers, conditional formatting, etc.).

Example VBA Code:

Sub ConsolidateData()
    ' Declare variables
    Dim wsConsolidation As Worksheet
    Dim wbSource As Workbook
    Dim wsSource As Worksheet
    Dim lastRowConsolidation As Long
    Dim lastRowSource As Long
    Dim sourceFile As String
    Dim sourcePath As String
    Dim i As Integer
    Dim files As Variant    
    ' Create or activate the consolidation sheet
    On Error Resume Next
    Set wsConsolidation = ThisWorkbook.Sheets("Consolidation")
    On Error GoTo 0    
    If wsConsolidation Is Nothing Then
        Set wsConsolidation = ThisWorkbook.Sheets.Add
        wsConsolidation.Name = "Consolidation"
    End If    
    ' Clear previous data in the consolidation sheet
    wsConsolidation.Cells.Clear    
    ' Add column headers (customize based on your needs)
    wsConsolidation.Cells(1, 1).Value = "Name"
    wsConsolidation.Cells(1, 2).Value = "Surname"
    wsConsolidation.Cells(1, 3).Value = "Age"
    wsConsolidation.Cells(1, 4).Value = "Registration Date"    
    ' Define the source file path (adjust this path based on your setup)
    sourcePath = "C:\Users\YourName\Documents\Excel\Sources\"    
    ' List of files to consolidate (you can add more files here)
    files = Array("File1.xlsx", "File2.xlsx", "File3.xlsx")    
    ' Loop through each file
    For i = LBound(files) To UBound(files)        
        ' Open the source file
        sourceFile = sourcePath & files(i)
        Set wbSource = Workbooks.Open(sourceFile)        
        ' Loop through each sheet in the source workbook
        For Each wsSource In wbSource.Sheets            
            ' Find the last used row in the source sheet
            lastRowSource = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row            
            ' Find the first empty row in the consolidation sheet
            lastRowConsolidation = wsConsolidation.Cells(wsConsolidation.Rows.Count, "A").End(xlUp).Row + 1            
            ' Copy the data from the source sheet to the consolidation sheet
            wsSource.Range("A2:D" & lastRowSource).Copy wsConsolidation.Range("A" & lastRowConsolidation)
        Next wsSource        
        ' Close the source file without saving
        wbSource.Close False
    Next i    
    ' Remove duplicates in the consolidation sheet
    wsConsolidation.Range("A1:D" & wsConsolidation.Cells(wsConsolidation.Rows.Count, "A").End(xlUp).Row).RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes    
    ' Apply formatting to the consolidation sheet (optional)
    wsConsolidation.Columns.AutoFit
    wsConsolidation.Rows(1).Font.Bold = True
    wsConsolidation.Rows(1).HorizontalAlignment = xlCenter    
    ' Display a message indicating the process is complete
    MsgBox "Data consolidation is complete.", vbInformation    
End Sub

Detailed Explanation of the Code:

1. Creating and Preparing the Consolidation Sheet:
   • The code checks if a sheet named Consolidation already exists in the current workbook. If it doesn’t, it creates a new sheet with that name.
   • It then adds column headers to the first row of the consolidation sheet.
2. Defining the Source Files Path:
   • The path to the source files is specified in the sourcePath variable. You should adjust this path to where your source files are stored.
   • The list of files to be consolidated is stored in the files array. You can modify this array to include all the files you want to consolidate.
3. Looping Through Each Source File:
   • For each file in the files array, the code opens the file and loops through all the sheets in the workbook.
   • It finds the last used row in each source sheet and the next available row in the consolidation sheet.
   • The code then copies the data (from row 2 onward, to exclude headers) from the source sheet to the consolidation sheet.
4. Removing Duplicates:
   • After all data has been copied to the consolidation sheet, the code removes duplicates based on columns 1, 2, and 3 (Name, Surname, and Age).
   • You can change the columns in the RemoveDuplicates method if you want to base duplicates on other criteria.
5. Applying Formatting:
   • The code auto-fits the columns to adjust the width based on the data.
   • It applies bold formatting to the first row (headers) and centers the text in the headers.
6. Completion Message:
   • Once the consolidation is complete, a message box will appear, letting you know that the process is finished.

Customization:

• File Names and Columns: Modify the files array with the actual file names and adjust the column headers in the consolidation sheet according to your data structure.
• Duplicate Removal: If you want to remove duplicates based on other columns, modify the RemoveDuplicates line to include the relevant column indexes.
• Additional Formatting: You can add more formatting styles (like cell colors, fonts, etc.) if needed in the section for formatting.

Conclusion:

This VBA script will automate the process of consolidating data from multiple Excel files or sheets into one master sheet. You can further customize it to fit your specific requirements, such as handling different types of data or applying more complex formatting.