Lookup data via a filter in VBA

[Eurekaonide]

Hi
Please can you help. I have a set of data in one sheet that I want to run down Column A and use the value in each cell to lookup via a filter in another sheet.

If I run the record macro it specifies the actual data but this data can be different all the time, so how would I reference this?
i.e Call A2 currently states my Name "Kellyn" but next time it could be "Joe" in A2 that I want to look up.

Sub Macro1()
    Range("A2").Select
    Selection.Copy
    Sheets("JustOurs_tmp").Select
    ActiveSheet.Range("$A$2:$BB$1453").AutoFilter Field:=1, Criteria1:= _
        "Kellyn"
    ActiveWindow.SmallScroll Down:=-15
    Range("A741").Select
End Sub

 

[davesexcel]

Criteria1:=range("A2").value

 

[Eurekaonide]

Criteria1:=range("A2").value

Thank you so much for this, how would I get it to loop? as the first item to look up and filter is A2 then the next is A3 then A4 etc until the end of the data set which I wont know how long it is?

Im really stuck on this so would really appreciate all the help I can get. If Filter is not the best way to find the data set.

Once I have found the data by the filter I then need to copy over new data to that row then move to the next one.

 

[Eurekaonide]

Hi I have found this as an almost solution

Sub ExcelTze()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("Changes")
    Set ws2 = Worksheets("TestRDEL")
    
    Dim c As Range, rng As Range, fnd As Range
    Set rng = ws2.Range("A2", ws2.Cells(Rows.Count, "A").End(xlUp))
    
    For Each c In ws1.Range("A2", ws1.Cells(Rows.Count, "A").End(xlUp))
        If c <> "" Then
            Set fnd = rng.Find(c.Value, , xlFormulas, xlWhole)
            If Not fnd Is Nothing Then
                c.Offset(, 1).Value = fnd.Offset(, 2).Value
            End If
        End If
    Next c
End Sub

However I need it to be pasted in the TestRDEL sheet not the Changes sheet. It should be looking up from the Changes sheet and pasting the updates in the TESTRDEL sheet and currently its only doing the second column I need it to update 19 columns to the right so from B:T
can anyone help me adjust it please? Thanks in advance.

 

[davesexcel]

c.Offset(, 1).Value = fnd.Offset(, 2).Value

possibly,
You may need to edit to suite, but you get the idea

ws1.range("B" & c.row & ":T" & c.row).value=ws2.range("C" & fnd.row & ":U" & fnd.row).value

 

[Eurekaonide]

possibly,
You may need to edit to suite, but you get the idea

ws1.range("B" & c.row & ":T" & c.row).value=ws2.range("C" & fnd.row & ":U" & fnd.row).value

Oh My Goodness - thank you so very very much its worked a dream Cant thank you enough