how to consolidate data in excel using code

#How to consolidate data in excel using code how to

Sh.UsedRange.Copy dsh.Range('A' & lr + 1)ĭsh.UsedRange.AutoFilter 1, dsh.Range('A1').Valueĭsh.Range('A2:A' & ).SpecialCells(xlCellTypeVisible).EntireRow. Excel VBA can be used to loop through the worksheets in a file, use the autofilter to isolate criteria and copy the information to the last used row in a sheet.

Option Explicitįile_Name = Application.GetOpenFileName('Excel Files (*.xlsx),*.xlsx, Text Files (*.txt),*.txt', 1, 'Select Excel Files to Consolidate',, True)įor i = LBound(File_Name) To UBound(File_Name) Create a Pivot Table using data from different sheets in a workbook, or from different workbooks, if those tables have identical column structures. Data will be consolidated on “ Data” worksheet of Macro File. Copy this code and paste it in VBA module. The constructs used by the statement that finds. Application.GetOpenFileName is used to allow the user to select the files on run time.īelow is the code to consolidate the data from multiple Excel files. myLastRow is set to the number of the last row with data in the worksheet named Merge Cells Based on Criteria.

#How to consolidate data in excel using code how to

In this article you will learn how to consolidate the data from multiple excel files using Application.GetOpenFileName in VBA.