Attribute VB_Name = "ConsolidateSheetsIntoOne"
Option Explicit

Public Sub ConsolidateSheetsIntoOne()
    Dim ws As Worksheet
    Dim destWs As Worksheet
    Dim destRow As Long
    Dim isFirst As Boolean

    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets("Consolidated").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

    Set destWs = ThisWorkbook.Worksheets.Add(Before:=ThisWorkbook.Worksheets(1))
    destWs.Name = "Consolidated"
    destRow = 1
    isFirst = True

    Application.ScreenUpdating = False
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Consolidated" Then
            Dim startRow As Long
            startRow = IIf(isFirst, 1, 2)
            ws.UsedRange.Offset(startRow - 1, 0).Resize(ws.UsedRange.Rows.Count - startRow + 1).Copy _
                destWs.Cells(destRow, 1)
            destRow = destWs.Cells(destWs.Rows.Count, 1).End(xlUp).Row + 1
            isFirst = False
        End If
    Next ws
    Application.ScreenUpdating = True

    MsgBox "Consolidation complete.", vbInformation
End Sub