In this article you will learn how to consolidate the data from multiple excel files using Application.GetOpenFileName in VBA. Application.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")
dsh.UsedRange.Clear
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.Range("1:1").Delete
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:
