'This function loops through all Excel files available in the folder
 'and consolidates the data of active sheet
 Public Sub ConsolidateFiles()
     '
     Dim objFileSys As Object
     Dim objFiles As Object
     Dim objFile As Object
     Dim strExtn As String
     Dim bIsHeaderAdded As Boolean
     Dim wkbFile As Workbook
     Dim wksSheet As Worksheet
     Dim lConsolCounter As Long
     '
     'Validate
     If Trim(wksConsol.Range("C3").Value) = "" Then
         MsgBox "Please select a folder to proceed", vbInformation
         Exit Sub
     End If
     '
     'Clear old data
     wksConsol.Range("5:" & wksConsol.Cells.SpecialCells(xlCellTypeLastCell).Row + 10).Delete
     '
     On Error GoTo Error_Import
     '
     Set objFileSys = CreateObject("Scripting.FileSystemobject")
     Set objFiles = objFileSys.GetFolder(wksConsol.Range("C3").Value & "\")
     '
     bIsHeaderAdded = False
     lConsolCounter = 6
     For Each objFile In objFiles.Files
         strExtn = LCase(Right(objFile.Path, InStr(1, StrReverse(objFile.Path), ".")))
         If strExtn = ".xls" Or strExtn = ".xlsx" Or strExtn = ".xlsm" Then
             'Open the file
             Set wkbFile = Workbooks.Open(objFile.Path, False, True)
             Set wksSheet = wkbFile.ActiveSheet
             '
             'Copy header
             If bIsHeaderAdded = False Then
                 wksSheet.Range("1:1").Copy wksConsol.Range("A5")
                 bIsHeaderAdded = True
             End If
             '
             'Copy content
             If wksSheet.Cells.SpecialCells(xlCellTypeLastCell).Row > 1 Then
                 wksSheet.Range("2:" & wksSheet.Cells.SpecialCells(xlCellTypeLastCell).Row).Copy wksConsol.Range("A" & lConsolCounter)
                 lConsolCounter = lConsolCounter + wksSheet.Cells.SpecialCells(xlCellTypeLastCell).Row - 1
             End If
             '
             'Close the file
             wkbFile.Close False
         End If
     Next
     '
     MsgBox "Done", vbInformation
     '
     Exit Sub
     '
 Error_Import:
     MsgBox "Unable to consolidate files" & vbNewLine & vbNewLine & "Error: " & Err.Description, vbCritical
     Exit Sub
     '
 End Sub