Option Explicit

Public Sub Ячейки_В_Столбец()
  Dim rng_Sour As Range, eL As Range, Row_Last As Long
  Set rng_Sour = Worksheets("Лист1").UsedRange
  
  With Worksheets("Лист2")
    For Each eL In rng_Sour
      If InStr(eL.Value, "гамак") > 0 Then
        Row_Last = .[a1].CurrentRegion.Rows.Count + 1
        .Cells(Row_Last, 1).Value = eL.Value
        End If
    Next
  End With
End Sub