Copy the row to another sheet when the value of a cell matches

[Ghuphuneho]

Hi,

I have a problem that I could not solve.

I have two tables, where in column "A" of table 1 has values ​​that appear in column "A" of table 2 (these tables are on separate sheets).

The data in column "A" in table 1 is never repeated.
The data in column "A" in table 2 are the same as in column "A" in table 1 but they are repeated.

I need a macro that looks for each of the cells in column A of table 1 in column A of table 2, and when they match, copy the row in table 1 and paste it over the row in table 2.

The data in table 1 should only be copied once in table 2, on the first cell that matches

I leave a sample file in case they need it, and this is the code that I have been occupying but it doesn't work for me.

Please Login or Register  to view this content.

Thank you!!!

[maras_mak]

Does my code give you the expected results?
Working time approx. 90 seconds.

Please Login or Register  to view this content.

[Marc L]

A starter demonstration lasting around a second :

Sub Demo1() 
    Dim H, V, R&, W, Rw As Range, C% 
        H = Application.Index(Hoja1.UsedRange.Rows(1).Value2, 0) 
        V = H 
    For R = 1 To UBound(H) 
        W = Application.Match(H(R) & "*", Hoja2.UsedRange.Rows(1), 0) 
        H(R) = IIf(IsNumeric(W), W, False) 
        V(R) = IIf(IsNumeric(W), R, False) 
    Next 
        H = Filter(H, False, False):  If UBound(H) < 0 Then Beep: Exit Sub 
        H = Evaluate("{" & Join(H, ",") & "}") 
        V = Filter(V, False, False) 
        Application.ScreenUpdating = False 
    With CreateObject("Scripting.Dictionary") 
        For Each Rw In Hoja1.UsedRange.Rows("2:" & Hoja1.UsedRange.Rows.Count) 
            .Add Rw.Cells(1).Value2, Application.Index(Rw.Value2, , V) 
        Next 
            V = Hoja2.UsedRange.Columns(1).Value2 
        For R = 2 To UBound(V) 
            If .Exists(V(R, 1)) Then 
                W = .Item(V(R, 1)) 
                For C = 1 To UBound(H):  Hoja2.Cells(R, H(C)).Value2 = W(C):  Next 
               .Remove V(R, 1) 
                If .Count = 0 Then Exit For 
            End If 
        Next 
           .RemoveAll 
    End With 
        Application.ScreenUpdating = True 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » !

[YasserKhalil]

@MarcL
Thanks a lot for your masterpieces ..
As for this line

Please Login or Register  to view this content.

What if I would like to hard-code the v variable ..
I tried this

Please Login or Register  to view this content.

But doesn't work ..

[Marc L]

As the V variable is an array like the one you have hardcoded as you can check in the Locals window

and the result with Z = Application.Index(Rw.Value2, , V) …

As H is the destination columns and V is the source columns, when you hardcode V do not forget H, did you ?

[YasserKhalil]

Thanks a lot. I got it now
Thank you very much

[Ghuphuneho]

Thank you very much everyone for the help!!!
Everything works perfectly and I was exploding my head trying to achieve this.

The problem is solved.