|Another option is to use the .Find method instead of checking every cell for the value "Jack". |
In addition, using sht2_Row = sht2_Row + 1 could be problematic since sht2_Row will be 0 each time the code is run, which could result in data being overwritten. This could be an issue if there are column headings.
While both methods certainly work, the best method would depend on the OP's needs.
Here is another Macro option, again assuming that "Jack" resides in Column A:
Application.ScreenUpdating = False
Dim sht1_lastRow, nxt_sht2_Row As Integer
Dim j As Range, firstAddress As String
'Find length of Column A
sht1_lastRow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row
'Check for Jack in Column A
Set j = .Find("Jack", lookat:=xlWhole)
'Copy row to Sheet2 if Jack is found
If Not j Is Nothing Then
firstAddress = j.Address
nxt_sht2_Row = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1
j.EntireRow.Copy Destination:=Sheets(2).Cells(nxt_sht2_Row, "A")
'Try to Find Jack again
Set j = .FindNext(j)
'Loop if found
Loop While Not j Is Nothing And j.Address <> firstAddress
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.