EXCEL VBA: COMBINING EXCEL FILES TO A SINGLE WORKSHEET IN A NEW FILE

Demand for automation is ever increasing. Excel data is no exception as enterprises have accumulated huge repositories of data in excel files over the years. Most a times there is need to combine these files into one to facilitate ingestion and analysis.

The below routine combines all excel files in the specified path into one worksheet in a new workbook.

a.     All the files you want to combine must be in the same folder.

b.     The files contain a single sheet each. When there are multiple sheets per file the routine should be amended.

c.     The files contain the same data (column headers, number of columns, column data types). The routine will work regardless but logically you would want to combine similar data sets.

 The routine recursively opens each file in the folder, copies the data using range copy and appends after the last row in the new workbook.

Do While Len(Filename) > 0

Set wbk = Workbooks.Open(Path & Filename)

wbk.Activate

colCount = wbk.Sheets(1).Cells(1, 255).End(xlToLeft).Column

Set rng = wbk.Sheets(1).Range(wbk.Sheets(1).Cells(2, 1), wbk.Sheets(1).Cells(65536, 1).End(xlUp).Resize(, colCount))

Windows("test1.xlsm").Activate

Application.DisplayAlerts = False

Dim lr As Double

lr = NewWorkBook.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row

If lr = 1 Then

    With NewWorkBook.Sheets(1).Cells(1, 1).Resize(1, colCount)

       .Value = wbk.Sheets(1).Cells(1, 1).Resize(1, colCount).Value

          .Font.Bold = True

   End With

End If

rng.Copy Destination:= NewWorkBook.Sheets(1).Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count)

wbk.Close True

Filename = Dir

Loop

To use the routine you need to declare variable and assign them values. It works for any number of files.