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

**kkihead** · 08-22-2013, 01:00 PM

Hello HaHoBe,
I tried yours and it said script out of range. Was I meant to change any of the values?

patel45's worked great but I am wondering if there is a way for the sheets to update automatically as something is entered?

Anytime I change anything in the log, I will have to delete what is in each sheet and run the macros again for them to be updated without a bunch of duplicate lines.

**HaHoBe** · 08-22-2013, 01:12 PM

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