is there a way to look for specific word in cells even if it's within more text i've been trying below but it only works if the only data exsits within a cell is the word.
Sub Tucana()
Dim sh1 As Worksheet, sh2 As Worksheet, rng As Range, cel As Range
Dim rngCopy As Range, lastR1 As Long, lastR2 As Long
Dim strSearch1 As String, strSearch2 As String
strSearch1 = "Tucana"
strSearch2 = "Tuc"
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
lastR1 = sh1.Range("A" & Rows.Count).End(xlUp).Row
lastR2 = sh2.Range("A" & Rows.Count).End(xlUp).Row + 1
Set rng = sh1.Range("A1:A" & lastR1)
For Each cel In rng.Cells
If cel.Value = strSearch1 Or cel.Value = strSearch2 Then
If rngCopy Is Nothing Then
Set rngCopy = sh1.Rows(cel.Row)
Else
Set rngCopy = Union(rngCopy, sh1.Rows(cel.Row))
End If
End If
Next
If Not rngCopy Is Nothing Then
rngCopy.Copy Destination:=sh2.Cells(lastR2, 1)
End If
End Sub