Excel vba copy Row from one sheet to another and past it in the same Position

Question

I am Trying to build a vba script for excel with to check if value (ex: First and Last Name) in sheet1 exist in sheet2 then if exist copy the entire row from sheet1 and past it in sheet2 in the same position where it find it

i succeeded to check if the Name exist and past it
but in the end table not where it find it

Sub test()
Dim LR As Long, i As Long
LR = Range("C" & Rows.Count).End(xlUp).Row
LR2 = ThisWorkbook.Sheets("Feuil2").Range("C" & Rows.Count).End(xlUp).Row
For i = 14 To LR
    For j = 14 To LR2
    If Range("C" & i).Value = ThisWorkbook.Sheets("Feuil2").Range("C" & j).Value Then Rows(i).Copy Destination:=Sheets("Feuil2").Range("A" & Rows.Count).End(xlUp).Offset(1)
Next j
Next i
End Sub

any ideas ?? Thank you !

Would the place to paste be Destination:=Sheets("Feuil2").Range("A" & j) Wouldn't "j" be the row that the item was found? — Davesexcel
Yes already tried it but for some reason it keep copy it in the same first row 14 — Amine Mostefaoui

Davesexcel Davesexcel · Accepted Answer · 2015-07-12T15:28:09

You can also use find to find the value and get the row number.

Sub test2()
    Dim Rws As Long, rng As Range, c As Range, sh As Worksheet, Fx As Range

    Rws = Cells(Rows.Count, "A").End(xlUp).Row
    Set rng = Range(Cells(14, "C"), Cells(Rws, "C"))
    Set sh = Sheets("Feuil2")

    For Each c In rng.Cells

        With sh

            Set Fx = .Columns(3).Find(what:=c, lookat:=xlWhole)

            If Not Fx Is Nothing Then

                c.Offset(0, -2).Range("A1:B1").Copy sh.Range("A" & Fx.Row)

            End If

        End With

    Next c

End Sub

Excel vba copy Row from one sheet to another and past it in the same Position

1 Answers