Automatically Copy Cells to Another Worksheet Based on Specific Words

[JKnight11]

When the word "Overdue" appears in a cell (in column H), I want the name (in column A) and the date (in column F) in that row to automatically copy and paste into another worksheet (named HomePage) and appear in column C12 and E12.
I have the following code, but it's cutting and pasting the entire row. I just want a copy and paste of the name and date to my HomePage.
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">Private Sub Worksheet_Change (ByVal Target As Range)
If Target.Column = 8 Then
If Target.Value = "Overdue" Then
R = Target.Row
Rows(R).Cut
Worksheets("HomePage").Select
With ActiveSheet
lastrow = .Cells(.Rows.Count, "C").End(xlUp).Row + 1
.Cells(lastrow, 1).Select
.Paste
End With
End If
End If
End Sub</code>

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("H:H")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim r As Long
Dim Lastrow As Long
    If Target.Value = "Overdue" Then
        r = Target.Row
        Lastrow = Sheets("HomePage").Cells(Rows.Count, "C").End(xlUp).Row + 1
        Cells(r, "A").Copy Destination:=Sheets("HomePage").Cells(Lastrow, "C")
        Cells(r, "F").Copy Destination:=Sheets("HomePage").Cells(Lastrow, "E")
    End If
        
End If
End Sub

 

[JKnight11]

Re: Automatically Copy Cells to Another Worksheet Based on Specific Words (SOLVED)

This worked perfectly!! Thank you so much!

 

[My Aswer Is This]

Re: Automatically Copy Cells to Another Worksheet Based on Specific Words (SOLVED)

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

This worked perfectly!! Thank you so much!