VBA; find string and copy

You can adjust the sheet numbers & column numbers in this to suit your needs

Option Explicit

Private Sub Workbook_Open()
    Dim rCopy  As Range
    Dim i      As Integer
    i = 2  'sheet to start from
    Do While i < 6
        With Worksheets(i)
            'copy cells with values in Column A
            Set rCopy = .Columns(1).SpecialCells(xlCellTypeConstants)
            rCopy.Copy Worksheets(3).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
        End With
        i = i + 1
    Loop
End Sub