VBA: Search for text in row & copy last cell/row in that column

[Eyes0ftheworld]

Hi!

I have a list of search terms in Sheet1 starting at A1. The length of the list varies. I need it to loop through that list of search terms & search Sheet2 for those terms. The terms will be somewhere in row 10 of Sheet2. Once it finds a match, I need it to find the last row in that column and copy that cell & paste value in Sheet2, column K in the next black row.
I know this is kind of complicated, but I'm struggling with it.

Thanks!

 

[mumps]

Try:

Sub CopyCell()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim Lastrow2 As Long
    Dim Term As Range
    Dim foundTerm As Range
    For Each Term In Sheets("Sheet1").Range("A1:A" & LastRow)
        Set foundTerm = Sheets("Sheet2").Rows(10).Find(Term, LookIn:=xlValues, lookat:=xlWhole)
        If Not foundTerm Is Nothing Then
            Lastrow2 = Sheets("Sheet2").Cells(Rows.Count, foundTerm.Column).End(xlUp).Row
            Sheets("Sheet2").Cells(Lastrow2, foundTerm.Column).Copy
            Sheets("Sheet2").Cells(Rows.Count, "K").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
            Application.CutCopyMode = False
        End If
    Next Term
    Application.ScreenUpdating = True
End Sub