Sub Macro1()
Dim strConsTabName As String
Dim varConsSheetLen As Variant
Dim lngLastRow As Long, _
lngPasteRow As Long
strConsTabName = "Consolidation" 'Consolidation tab name. Change to suit.
Application.ScreenUpdating = False
'If there's no tab called by the named passed to the 'strConsTabName' variable, then...
On Error Resume Next
varConsSheetLen = Len(Sheets(strConsTabName).Name)
If IsEmpty(varConsSheetLen) = True Then
'...create it at the at the start (furtherest left) of the workbook.
Worksheets.Add Before:=Sheets(1)
Sheets(1).Name = strConsTabName
'Else...
Else
'...delete it and recreate it (simply clearing the data may be another option).
With Application
.DisplayAlerts = False
Sheets(strConsTabName).Delete
.DisplayAlerts = True
Worksheets.Add Before:=Sheets(1)
Sheets(1).Name = strConsTabName
End With
End If
On Error GoTo 0
For Each Worksheet In ThisWorkbook.Sheets
If Worksheet.Name <> strConsTabName Then
'Find where the last row resides from Col's A to H
lngLastRow = Worksheet.Range("A:H").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
'Set the 'lngPasteRow' variable.
If WorksheetFunction.CountA(Sheets(strConsTabName).Cells) = 0 Then
lngPasteRow = 1
Else
lngPasteRow = Sheets(strConsTabName).Range("A:H").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
End If
Worksheet.Range("A1:H" & lngLastRow).Copy _
Destination:=Sheets(strConsTabName).Range("A" & lngPasteRow)
End If
Next Worksheet
Application.ScreenUpdating = True
End Sub