Untested (ie coded on the fly) but perhaps:
Sub Collate()
Dim ws As Worksheet, wsFinal As Worksheet, rngCopy As Range, xlCalc As xlCalculation
On Error GoTo ExitPoint
With Application
xlCalc = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
Set wsFinal = Sheets("FCombined")
With wsFinal
.Range(.Cells(2, "A"), .Cells(.Rows.Count, "A")).Resize(,13).Clear
End With
For Each ws In ThisWorkbook.Worksheets
Select Case UCase(ws.Name)
Case "F1", "F2", "F3", "F4"
With ws
Set rngCopy = .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp)).Resize(, 13)
End With
With wsFinal
.Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(rngCopy.Rows.Count, rngCopy.Columns.Count) = rngCopy.Value
End With
End Select
Next ws
ExitPoint:
Set wsFinal = Nothing
With Application
.Calculation = xlCalc
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
the above assumes
a) final sheet has header row
b) final sheet should be purged with each collation
c) values only copy
EDIT: 12:14 - missing comma in the .Clear line at beginning of routine - did say it was untested
Bookmarks