Pulling Data From Sheet To Sheet Based on Text

Your post is lacking in details, but perhaps you can adapt this bit of code as a start. See attached for demo.

Sub Compile_Sheets()

    Dim ws As Worksheet
    Dim lSheets As Long
    
    lSheets = Worksheets.Count
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    On Error Resume Next
    
    For i = 1 To lSheets
        For Each ws In Worksheets
            With ws.Range("A1").CurrentRegion
                .Copy
                Sheet1.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
                Application.CutCopyMode = False
            End With
        Next ws
        i = i + 1
    Next i

    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub