Sub consolidation()
Dim sh As Worksheet
Dim sh_count As Integer
Dim lr As Integer
Dim lc As Integer
Dim con_lr_count As Integer
Dim sht_name As String
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Consolidation"
For Each sh In Sheets
If sh.Name <> "Consolidation" Then
con_lr_count = Sheets("Consolidation").Cells(Rows.Count, 1).End(xlUp).Row + 1
sht_name = sh.Name
lr = Sheets(sht_name).Cells(Rows.Count, 1).End(xlUp).Row
lc = Sheets(sht_name).Cells(1, Columns.Count).End(xlToLeft).Column
Sheets(sht_name).Range(Cells(1, 1), Cells(lr, lc)).Select
Selection.Copy Sheets("Consolidation").Range("A" & con_lr_count)
End If
Next
End Sub
edit for readability - Karolus