Option Explicit
Sub Test()
Dim foundRng As Range
Sheets("Sheet1").Select
Set foundRng = Range("A1").CurrentRegion.Find("*Cable*", LookIn:=xlFormulas)
foundRng.Rows("1:1").EntireRow.Select
Selection.Copy
Sheets("Sheet2").Activate
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
ActiveSheet.Paste
End Sub
