Try changing the consolidation macro to this version, which will actually copy the cells (including formatting) rather than just using the values:
Sub Consolidation()
Dim wbk As Workbook, wbkMaster As Workbook
Dim wksSource As Worksheet, wksDest As Worksheet
Dim StrFile As String, strPath As String
Dim rngLastCell As Range
Dim lngRowCount As Long, lngTargRow As Long, lngCounter As Long
Dim varData
Dim strSlash As String
strSlash = Application.PathSeparator
' Note: to use fixed path, uncomment next line and change to whatever path you want
' strPath = "C:\Test"
' Allows you to pick a folder each time
strPath = GetFolder
If strPath = "" Then
MsgBox "You must choose a path!"
Exit Sub
End If
Application.ScreenUpdating = False
lngCounter = 1
If Right$(strPath, 1) <> strSlash Then strPath = strPath & strSlash
If Dir(strPath & "cleaned", vbDirectory) = "" Then MkDir strPath & "cleaned"
StrFile = Dir(strPath & "*.xls")
Set wbkMaster = Workbooks.Add
Set wksDest = wbkMaster.Worksheets(1)
wksDest.Name = "Data1"
lngTargRow = 2
Do Until StrFile = ""
If Not StrFile = ThisWorkbook.Name Then
Set wbk = Workbooks.Open(strPath & StrFile)
If CleanFile(StrFile) = True Then
' Assumes only one sheet
Set wksSource = wbk.Worksheets(1)
Set rngLastCell = LastCellInSheet(wksSource)
lngRowCount = rngLastCell.Row - 1
If lngTargRow + lngRowCount - 1 > 65536 Then
lngCounter = lngCounter + 1
Set wksDest = wbkMaster.Sheets.Add
wksDest.Name = "Data " & lngCounter
lngTargRow = 2
End If
With wksSource
.Range(.Cells(2, "A"), rngLastCell).Copy wksDest.Cells(lngTargRow, 1)
End With
lngTargRow = lngTargRow + lngRowCount
End If
wbk.SaveAs strPath & "cleaned" & strSlash & Replace$(wbk.Name, ".xls", "") _
& " clean " & Format$(Now, "yyyy-mm-dd hh.mm") & ".xls"
wbk.Close False
End If
StrFile = Dir
Loop
wbkMaster.SaveAs strPath & "Master.xls"
' wbkmaster.close
Application.ScreenUpdating = True
End Sub