Master Advanced Excel & VBA

Learn Excel formulas, data analysis, dashboards, VBA programming, and business automation through structured practical lessons.

COURSE SYLLABUS
1. Excel Basics
Introduction to Advanced Excel Workbook, Worksheets, Rows & Columns Data Types & Cell Formatting Cell References & Keyboard Shortcuts
2. Advanced Excel Formulas
Advanced Formulas Introduction IF, AND, OR & IFERROR Excel Tables XLOOKUP INDEX & MATCH SUMIFS & COUNTIFS
3. Pivot Analytics
Pivot Analytics Introduction Pivot Tables Calculated Fields & Items Timelines & Slicers Text Functions & Data Cleaning
4. Excel Dashboards
Interactive Dashboards Introduction Data Validation Conditional Formatting Combo Charts Sparklines
5. Excel Data Tools
Data Integration Introduction Importing Text Files Power Query Worksheet Protection Formula Auditing
6. VBA Fundamentals
VBA Fundamentals Introduction Macro Recorder VBE Workspace VBA Objects & Properties Variables & Data Types MsgBox & InputBox If...Then...Else Select Case Loops & Dynamic Ranges
7. VBA Automation
VBA Automation Introduction Subroutines & Arguments User-Defined Functions Workbook Events Worksheet Events Form Controls & Buttons
8. VBA UserForms
UserForms Introduction UserForm Design Toolbox Controls Input Validation CRUD Operations with UserForms
9. VBA Optimization & Security
Optimization Introduction Error Handling Debugging Tools Performance Optimization VBA Project Security
10. Corporate Capstone Project
Capstone Project Introduction Data Consolidation Data Cleaning & Analysis Automated PDF Reports Dashboard Automation
11. Excel Practice & Assignments
Practice Introduction Excel Formula Exercises Pivot Table Exercises Dashboard Exercises VBA Programming Exercises Final Assignments

Data Consolidation Using Excel VBA

Learn how to combine data from multiple Excel workbooks into one master worksheet using VBA. This tutorial covers the business scenario, workbook structure, VBA automation, error handling, output verification, and practical exercises.

1. What Is Data Consolidation?

Data Consolidation is the process of collecting data from multiple worksheets or workbooks and combining it into a single, organized dataset. It is commonly used when different branches, departments, or teams maintain separate Excel files.

For example, a company may maintain separate sales workbooks for Pune, Mumbai, and Nashik. The management team needs one report containing the sales records from all three branches.

Instead of manually copying and pasting the records, Excel VBA can automate the process and reduce repetitive work.

Learning objective: Build a VBA macro that reads data from multiple source workbooks, combines the records in a master worksheet, and preserves one common header row.

2. Understanding the Data Consolidation Process

Each source workbook contains its own sales records. The consolidation process copies the records into a central master worksheet. The header should normally appear only once.

Pune Branch Sales_Pune.xlsx Sales records Mumbai Branch Sales_Mumbai.xlsx Sales records Nashik Branch Sales_Nashik.xlsx Sales records Master Workbook MasterSales.xlsx Common Header Combined Branch Records One consolidated dataset

Diagram 1: Multiple source workbooks contribute records to one master workbook.

3. Real-World Business Scenario

Suppose the sales department receives three Excel files every month. Each file contains the same column structure but different branch records.

Order ID Branch Product Quantity Total Amount
ORD1001 Pune Laptop 2 90000
ORD1002 Mumbai Monitor 3 36000
ORD1003 Nashik Keyboard 5 7500

The master workbook should contain all source records in the same column order. In this example, the consolidated output contains three data rows and one header row.

Why Automate Consolidation?
  • Reduces repetitive copy-and-paste operations.
  • Provides a repeatable process for monthly reporting.
  • Reduces errors caused by manual copying.
  • Creates a central dataset for analysis and dashboards.
  • Can process additional source files without rewriting the entire workflow.

4. Preparing the Source and Master Workbooks

Before writing VBA code, standardize the source workbook structure. For this tutorial, every source file will contain a worksheet named Sales, with headers in row 1 and records beginning in row 2.

Recommended Folder Structure
Folder Structure
SalesAutomation/
|
|-- MasterSales.xlsm
|
|-- SourceFiles/
|   |-- Sales_Pune.xlsx
|   |-- Sales_Mumbai.xlsx
|   |-- Sales_Nashik.xlsx
|
|-- Reports/
Source Worksheet Structure
Column Field Name Example
A Order ID ORD1001
B Order Date 15-Oct-2026
C Branch Pune
D Product Laptop
E Quantity 2
F Unit Price 45000
G Total Amount 90000
Important: The sample macro assumes that all source files use the same seven-column structure and the worksheet name Sales. If your files use different headers or column positions, validate or map the columns before combining them.

5. Step-by-Step Consolidation Logic

The macro follows a predictable sequence so that each source workbook is processed in the correct order.

  1. Identify the folder containing source workbooks.
  2. Identify the master worksheet where data will be collected.
  3. Write the common column headers in the master worksheet.
  4. Find each Excel file in the source folder.
  5. Open the source workbook as read-only.
  6. Find the last populated row in the source worksheet.
  7. Copy the data rows, excluding the source header.
  8. Append the rows to the next available master row.
  9. Close the source workbook without saving changes.
  10. Repeat for the remaining source files.
  11. Format the output and display a completion message.

6. Data Consolidation Flowchart

The following flowchart shows how the macro handles each workbook until all source files have been processed.

Start Macro Prepare Master Worksheet Another Source File Available? Yes Open and Validate Source Append Data and Close File Repeat No Finish and Verify Output

Diagram 2: The macro processes a source file, appends its records, and repeats until no additional source file is available.

7. Complete VBA Program for Data Consolidation

This example consolidates all .xlsx files from a folder named SourceFiles, located beside the master workbook. Each source workbook must contain a worksheet named Sales with headers in row 1.

Before Running the Macro
  1. Save the master workbook as MasterSales.xlsm.
  2. Create a folder named SourceFiles beside it.
  3. Place the source workbooks inside that folder.
  4. Make sure each workbook contains the expected Sales worksheet.
  5. Press Alt + F11 and insert a standard module.
Complete VBA Code
Option Explicit

Sub ConsolidateSalesData()

    Dim masterWB As Workbook
    Dim masterWS As Worksheet
    Dim sourceWB As Workbook
    Dim sourceWS As Worksheet

    Dim sourceFolder As String
    Dim fileName As String
    Dim fullPath As String

    Dim lastSourceRow As Long
    Dim nextMasterRow As Long
    Dim copiedRows As Long

    Dim previousScreenUpdating As Boolean
    Dim previousEnableEvents As Boolean

    Dim errorMessage As String

    On Error GoTo ErrorHandler

    Set masterWB = ThisWorkbook

    ' The source folder is beside the master workbook.
    If Len(masterWB.Path) = 0 Then
        MsgBox "Please save the master workbook first.", vbExclamation
        Exit Sub
    End If

    sourceFolder = masterWB.Path & _
                   Application.PathSeparator & "SourceFiles" & _
                   Application.PathSeparator

    If Len(Dir(sourceFolder, vbDirectory)) = 0 Then
        MsgBox "SourceFiles folder was not found.", vbExclamation
        Exit Sub
    End If

    Set masterWS = masterWB.Worksheets("Master")

    previousScreenUpdating = Application.ScreenUpdating
    previousEnableEvents = Application.EnableEvents

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    ' Prepare the master worksheet.
    masterWS.Cells.Clear

    masterWS.Range("A1:G1").Value = Array( _
        "Order ID", _
        "Order Date", _
        "Branch", _
        "Product", _
        "Quantity", _
        "Unit Price", _
        "Total Amount")

    nextMasterRow = 2
    copiedRows = 0

    ' Find source Excel workbooks.
    fileName = Dir(sourceFolder & "*.xlsx")

    Do While Len(fileName) > 0

        ' Avoid processing temporary Excel lock files.
        If Left$(fileName, 2) <> "~$" Then

            fullPath = sourceFolder & fileName

            ' Open source workbook as read-only.
            Set sourceWB = Workbooks.Open( _
                Filename:=fullPath, _
                UpdateLinks:=0, _
                ReadOnly:=True, _
                AddToMru:=False)

            Set sourceWS = Nothing

            On Error Resume Next
            Set sourceWS = sourceWB.Worksheets("Sales")
            On Error GoTo ErrorHandler

            If sourceWS Is Nothing Then
                Err.Raise vbObjectError + 1001, _
                    "ConsolidateSalesData", _
                    "The Sales worksheet is missing in " & fileName
            End If

            ' Find the last populated row using Order ID column.
            lastSourceRow = sourceWS.Cells( _
                sourceWS.Rows.Count, "A").End(xlUp).Row

            If lastSourceRow >= 2 Then

                ' Copy data without copying the source header.
                masterWS.Cells(nextMasterRow, 1).Resize( _
                    lastSourceRow - 1, 7).Value = _
                    sourceWS.Range("A2:G" & lastSourceRow).Value

                copiedRows = copiedRows + lastSourceRow - 1

                nextMasterRow = nextMasterRow + lastSourceRow - 1

            End If

            sourceWB.Close SaveChanges:=False
            Set sourceWB = Nothing
            Set sourceWS = Nothing

        End If

        fileName = Dir()

    Loop

    ' Format the master worksheet.
    With masterWS

        .Range("A1:G1").Font.Bold = True
        .Range("A1:G1").Interior.Color = RGB(217, 225, 242)
        .Columns("A:G").AutoFit

        If nextMasterRow > 2 Then
            .Range("B2:B" & nextMasterRow - 1).NumberFormat = _
                "dd-mmm-yyyy"

            .Range("F2:G" & nextMasterRow - 1).NumberFormat = _
                "#,##0.00"
        End If

    End With

    MsgBox copiedRows & _
           " data rows consolidated successfully.", vbInformation

CleanExit:

    On Error Resume Next

    If Not sourceWB Is Nothing Then
        sourceWB.Close SaveChanges:=False
    End If

    Application.ScreenUpdating = previousScreenUpdating
    Application.EnableEvents = previousEnableEvents

    On Error GoTo 0
    Exit Sub

ErrorHandler:

    errorMessage = Err.Description

    MsgBox "Consolidation failed: " & errorMessage, vbExclamation

    Resume CleanExit

End Sub
Important: This example clears the existing contents of the Master worksheet before importing records. Use a dedicated output worksheet and back up important data before running it. The source workbook structure must match the assumed columns.

8. Detailed Explanation of the VBA Code

8.1 Declaring Variables
VBA Code
Dim masterWB As Workbook
Dim masterWS As Worksheet
Dim sourceWB As Workbook
Dim sourceWS As Worksheet

These variables represent the master workbook, master worksheet, source workbook, and source worksheet. Using explicit object variables makes the code easier to understand and maintain.

8.2 Building the Source Folder Path
VBA Code
sourceFolder = masterWB.Path & _
               Application.PathSeparator & "SourceFiles" & _
               Application.PathSeparator

masterWB.Path returns the folder where the master workbook is saved. Application.PathSeparator supplies the correct path separator. The code then adds the SourceFiles folder name.

8.3 Finding Excel Files
VBA Code
fileName = Dir(sourceFolder & "*.xlsx")

The Dir function finds a file matching the specified pattern. Calling Dir() without an argument retrieves the next matching file.

8.4 Finding the Last Data Row
VBA Code
lastSourceRow = sourceWS.Cells( _
    sourceWS.Rows.Count, "A").End(xlUp).Row

This starts at the bottom of column A and moves upward to the last non-empty cell. The example assumes that Order ID in column A is populated for every valid record.

8.5 Appending Data to the Master Worksheet
VBA Code
masterWS.Cells(nextMasterRow, 1).Resize( _
    lastSourceRow - 1, 7).Value = _
    sourceWS.Range("A2:G" & lastSourceRow).Value

This transfers the values from the source data range to the master worksheet. The source range starts at row 2, so its header is not copied. Using a range-to-range assignment is generally faster than copying cells individually.

8.6 Updating the Next Master Row
VBA Code
nextMasterRow = nextMasterRow + lastSourceRow - 1

After each source workbook is processed, the next available master row is advanced by the number of data rows imported.

9. Before and After Consolidation

Before consolidation, each branch has a separate workbook. After consolidation, all compatible records appear in one master worksheet with a single header.

Before Sales_Pune.xlsx Header + Pune records Sales_Mumbai.xlsx Header + Mumbai records Sales_Nashik.xlsx Header + Nashik records After One Common Header Pune Records Mumbai Records Nashik Records Consolidated master dataset One common structure makes reporting easier.

Diagram 3: Consolidation combines compatible data records into one master dataset while retaining a single header.

10. Common Errors and Their Solutions

Problem Possible Cause Solution
SourceFiles folder not found Incorrect folder name or location. Save the master workbook and verify the folder path.
Sales worksheet missing Different worksheet name in a source workbook. Use the expected name or update the validation logic.
Data copied into incorrect columns Source columns have a different order. Standardize or map the columns before importing.
Duplicate records in the master Source files contain duplicate data. Define a unique key and handle duplicates as a separate step.
Date or number values appear incorrectly Inconsistent source formats or data types. Validate data types and apply suitable number formats.
Master data disappears before import The example clears the Master worksheet at the start. Back up existing data and use a dedicated output worksheet.

11. Practical Exercises

  1. Create three source workbooks containing sales records.
  2. Consolidate all source workbooks into the Master worksheet.
  3. Modify the macro to display the number of imported rows.
  4. Test the macro when one source workbook contains only headers.
  5. Test the behavior when a source workbook lacks the Sales worksheet.
  6. Extend the program to handle additional supported Excel file types.
  7. Design a separate validation step for duplicate Order IDs.
  8. Record the source file name alongside each imported row as an audit field.

12. Important Points to Remember

  • Standardize the source workbook structure before consolidation.
  • Copy headers once and append only data rows.
  • Use a reliable key column to determine the last data row.
  • Open source workbooks as read-only when they only need to be read.
  • Close source workbooks without saving unintended changes.
  • Restore application settings after an error or successful run.
  • Consolidation does not automatically clean duplicates or validate business rules.
  • Verify the row count and sample records after processing.

Summary

Data Consolidation using Excel VBA combines records from multiple source workbooks into one master worksheet. The process involves preparing the folder structure, opening each source file, identifying its data range, appending records, and verifying the output.

A reliable consolidation macro should validate source files, handle errors, preserve a common column structure, and avoid overwriting important data unexpectedly. The consolidated dataset becomes the foundation for the next project stage: Data Cleaning and Analysis.