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: