Copy Row To Another Sheet Depending On Existence Of Specific Value In Specific Column

[Excel Fox]

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.

 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

[peter renton]

Hi
Thank you for the code,
I have replaced my old code with yours in mod 1 and it works from sheet1 to sheet 2, but when i add another sheet it does not copy from 2 to 3 do i need to do something else for it to recognise future added sheets???

Peter