Sub a()
Dim ws As Worksheet, x&
For Each ws In Worksheets
If ws.Name <> "Compilation" Then
x = ws.Cells(Rows.Count, "H").End(3).Row
ws.Range(ws.Cells(7, "B"), ws.Cells(x, "Q")).Copy
Sheets("Compilation").Cells(Rows.Count, "B").End(3)(2).PasteSpecial xlValues
Application.CutCopyMode = False
End If
Next
End Sub