Peter, you have been guided regarding thread posting. Next time, please adhere to Thread Posting guidelines.

Here's a modified version of your existing code. This can be used for all the sheets, except the last sheet in the workbook.
Code:
 Sub SearchForString()

    Dim LSearchRow As Integer
    Dim LCopyToRow As Integer
    On Error GoTo Err_Execute
    'Start search in row 6
    LSearchRow = 6
    'Start copying data to row 13 in Sheet2 (row counter variable)
    LCopyToRow = 13
    While Len(Range("A" & CStr(LSearchRow)).Value) > 0
        'If value in column E = "Copy", copy entire row to Sheet2
        If Range("E" & CStr(LSearchRow)).Value = "Copy" Then
            With Sheets(ActiveSheet.Next.Name)
                .Range(LCopyToRow & ":" & LCopyToRow).Value = _
                    Range(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Value
                .Cells(LCopyToRow, "E").ClearContents
            End With
            'Move counter to next row
            LCopyToRow = LCopyToRow + 1
        End If
        LSearchRow = LSearchRow + 1
    Wend
    
    'Position on cell A3
    Range("A3").Select
    MsgBox "All Selected Rows Have Been Copied To The Next Sheet ."
    Exit Sub
Err_Execute:
    MsgBox "An error occurred."
    
End Sub