VBA to find cell value and copy entire row to next available row on another sheet

here you go:

Sub copyto()

Dim Cell As Range, Ws As Worksheet

For Each Ws In ThisWorkbook.Worksheets

    If Ws.Name <> "SOIMPORT" Then Ws.Activate

    For Each Cell In Range("A2", Cells(Rows.Count, 1).End(xlUp))
    
        Cell.Activate
        
        If ActiveCell.Value = Right(ActiveSheet.Name, 1) Then
        
        ActiveCell.EntireRow.Copy

        Sheets(6).Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        
        End If
        
        Next Cell
        
        Next Ws
            
End Sub