Data Consolidator

Data Consolidation: Allow User to select files on Runtime

In this article you will learn how to consolidate the data from multiple excel files using Application.GetOpenFileName in VBAApplication.GetOpenFileName is used to allow the user to select the files on run time.

Below is the code to consolidate the data from multiple Excel files. Copy this code and paste it in VBA module. Data will be consolidated on “Data” worksheet of Macro File.

Option Explicit

Sub Consolidate_Data()

Dim wb As Workbook
Dim sh As Worksheet

Dim dsh As Worksheet
Set dsh = ThisWorkbook.Sheets("Data")


Dim File_Name As Variant

File_Name = Application.GetOpenFileName("Excel Files (*.xlsx),*.xlsx, Text Files (*.txt),*.txt", 1, "Select Excel Files to Consolidate", , True)

Dim i As Integer

Dim lr As Long

For i = LBound(File_Name) To UBound(File_Name)

lr = dsh.Range("A" & Application.Rows.Count).End(xlUp).Row

Set wb = Workbooks.Open(File_Name(i))
Set sh = wb.Sheets(1)

sh.UsedRange.Copy dsh.Range("A" & lr + 1)

wb.Close False

Next i

'============= Formatting ==============

dsh.UsedRange.AutoFilter 1, dsh.Range("A1").Value
dsh.Range("A2:A" & Application.Rows.Count).SpecialCells(xlCellTypeVisible).EntireRow.Delete
dsh.AutoFilterMode = False

With dsh.UsedRange
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.EntireRow.RowHeight = 15
.EntireColumn.ColumnWidth = 15
.Font.Name = "Calibri"
.Font.Size = 9
.Borders.LineStyle = xlHairline
End With

With dsh.Range("A1", dsh.Cells(1, Application.WorksheetFunction.CountA(dsh.Range("1:1"))))
.Font.Bold = True
.Interior.ColorIndex = 23
.Font.ColorIndex = 2
End With

End Sub


Click here to download this Excel File:


Watch step by step video tutorial:

My Name is PK. I am founder of I am a Microsoft Certified Professional. I have more than 12 years of experience in Data Visualization, Excel Automation and dashboard creation. Excel is my passion and I like to do always something innovative in Excel and share it with other people.