Copy a line with a specific key word into another sheet.

Sub a()
sharr = Array("Bill", "Tim", "Marty", "Dave")
With Sheets("Log")
  LR = .Cells(.Rows.Count, "D").End(xlUp).Row
  For j = 1 To LR
    eng = .Range("D" & j)
    sh = ""
    For e = 0 To 3
      If InStr(eng, sharr(e)) > 0 Then
        sh = sharr(e)
        Exit For
      End If
    Next
    If sh <> "" Then
      LRs = Sheets(sh).Cells(Rows.Count, "A").End(xlUp).Row + 1
      Rows(j).Copy Sheets(sh).Cells(LRs, 1)
    End If
  Next
End With
End Sub

Hi, kkihead,

maybe like this:

Sub EF949541()
Dim lngCounter As Long

With Sheets("Log")
  For lngCounter = 1 To .Cells(.Rows.Count, "D").End(xlUp).Row
    Select Case .Range("D" & lngCounter)
      Case "Bill", "Tim", "Marty", "Dave"
        .Rows(lngCounter).Copy Sheets(sh).Cells(Sheets(sh).Range("A" & Rows.Count).End(xlUp).Row + 1, 1)
      Case Else
    End Select
  Next lngCounter
End With

End Sub

@patel:
should it be

.Rows(j).Copy Sheets(sh).Cells(LRs, 1)

as this line is within the With-Statement?

Hi, kkihead,

regarding the run-time error (my bad I didnīt update the code accordingly):

Sub EF949541_2()
Dim lngCounter As Long

With Sheets("Log")
  For lngCounter = .Cells(.Rows.Count, "D").End(xlUp).Row To 1 Step -1
    Select Case .Range("D" & lngCounter)
      Case "Bill", "Tim", "Marty", "Dave"
        .Rows(lngCounter).Copy Sheets(.Range("D" & lngCounter).Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        .Rows(lngCounter).Delete
      Case Else
    End Select
  Next lngCounter
End With

End Sub

This code will take away the line from sheet Log. The other way is (as you mentioned) clear contents on all named sheets prior to running the macro, I wouldnīt take the Workjsheet_Change-event to trigger the macro after a change but use the Worksheet_Deactivate - you would need to activate any other sheet to view what has been copied.

Ciao,
Holger