Pulling Data from multiple sheets and inserting rows

Try this code

Option Explicit

Sub cons_data()
Dim i As Long, lrow As Long

Application.ScreenUpdating = False

Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = "Summary"
Worksheets(1).Rows(1).Copy Worksheets("Summary").Range("A1")

For i = 1 To Worksheets.Count
    With Worksheets(i)
        If .Name <> "Summary" Then
            lrow = .Range("A" & .Rows.Count).End(xlUp).Row
            .Range("A2:C" & lrow).Copy Worksheets("Summary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        End If
    End With
Next i

With Worksheets("Summary")
    .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=Range("C:C") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .Sort.SortFields.Add Key:=Range("A:A") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With .Sort
        .SetRange Range("A:C")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
End With

MsgBox "Consolidation complete"

Application.ScreenUpdating = True

End Sub

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button