Sub Macro4()
Dim colm As Long
Dim SourceSht As Worksheet, DestnSht As Worksheet, rngSourceColumnHeaders As Range, lr As Long, rngDestnHeaders As Range, rngDestnDataBody As Range
Application.ScreenUpdating = False
Set DestnSht = Sheets("Consolidated")
For Each SourceSht In Sheets
If InStr(1, SourceSht.Name, "latest", vbTextCompare) <> 0 Then
'at this point, SourceSht is the first sheet encountered which has 'latest' somewhere in its name .
With SourceSht
SceLC = .Cells(1, .Columns.Count).End(xlToLeft).Column
Set rngSourceColumnHeaders = .Range(.Cells(1, "F"), .Cells(1, SceLC))
SceVals = .Range(.Cells(2, 1), .Cells(.Cells(.Rows.Count, "A").End(xlUp).Row, SceLC)).Value
End With
With DestnSht
DestLR = .Cells(.Rows.Count, "A").End(xlUp).Row
Set rngDestnHeaders = .Cells(1, "G").Resize(, rngSourceColumnHeaders.Columns.Count)
Set rngDestnDataBody = rngDestnHeaders.Offset(1).Resize(DestLR - 1)
ReDim myresults(1 To rngDestnDataBody.Rows.Count, 1 To rngDestnDataBody.Columns.Count)
rngSourceColumnHeaders.Copy rngDestnHeaders.Cells(1)
DestnFirst4ColumnsVals = .Range(.Cells(2, 1), .Cells(DestLR, 4)).Value
For DestnRow = 1 To rngDestnDataBody.Rows.Count
For SceRow = 1 To UBound(SceVals)
AllSame = True
For colm = 1 To 4 'this is where it compares each sheet's columns A to D.
If DestnFirst4ColumnsVals(DestnRow, colm) <> SceVals(SceRow, colm) Then
AllSame = False
Exit For
End If
Next colm
If AllSame Then
For i = 1 To UBound(myresults, 2)
myresults(DestnRow, i) = SceVals(SceRow, i + 5)
Next i
End If
Next SceRow
Next DestnRow
rngDestnDataBody.Value = myresults
rngDestnHeaders.Cells(1) = SourceSht.Name
rngDestnHeaders.Cells(1).Interior.Color = 65535
rngDestnHeaders.Cells(1).Font.ColorIndex = xlAutomatic
Application.DisplayAlerts = False
SourceSht.Delete
Application.DisplayAlerts = True
End With
End If
Next
With DestnSht
.Name = "Latest Consolidated " & Format(Date, "dd-mmm-yy")
If .Range("H1").Value <> 0 Then
Range(.Columns("G:G"), .Columns("G:G").End(xlToRight)).ColumnWidth = 15
Else
Columns("G:G").ColumnWidth = 15
End If
End With
Application.ScreenUpdating = True
End Sub