Copy values to new sheet if "Key" does not exist with VBA

[Amarben]

I'm trying to create a Macro to copy the values from a table T1 of sheet1 and paste them into the bottom of table T2 of sheet2 if the value in cell G (key) of sheet1 does not exist in in cell G of sheet 2.
If it exists, just replace the values of cell M of sheet2 by the values of cell M of the sheet1.

Example, if column G sheet1 = 16 and 16 doesn't exist in culumn G of sheet2 then copy the entire row from sheet1 and paste it to the bottom of sheet2 .. and if 16 exist in culumn G of sheet 2, replace sa equivalent value in column M

Can someone help me ?
Thanks in advance!

 

[Zot]

When you say key cell G, what is the row number?

 

[Amarben]

When you say key cell G, what is the row number?

in the both sheets (sheet1 & sheet2), it starts with G2

 

[Zot]

in the both sheets (sheet1 & sheet2), it starts with G2

Sound like:
Say Sheet1 G2 = 16. Find if 16 exists in Sheet2 column G, then
Copy entire row. Row 2 (follow row number on Sheet1) if not found and copy to bottom of T2 in Sheet 2
If found copy Sheet1 M2 into Sheet 2 M2

Then repeat for G3, G4, G5 and so on until finish data?

 

[Amarben]

Sound like:
Say Sheet1 G2 = 16. Find if 16 exists in Sheet2 column G, then
Copy entire row. Row 2 (follow row number on Sheet1) if not found and copy to bottom of T2 in Sheet 2
If found copy Sheet1 M2 into Sheet 2 M2

Then repeat for G3, G4, G5 and so on until finish data?

Yes that's exactly it, thank you for your reaction

 

[Zot]

Not sure if this is correct
You have
1) Table1 in Sheet1
2) Table2 in Sheet2
3) You have data in column G in Sheet1 and Sheet2
4) You have data in column M in Sheet1 and Sheet2

The code will
Loop from G2 down in Sheet1 and check if the value exist column G in Sheet2
If exist copy value Sheet1 column M in corresponding row to Sheet2 column M in corresponding row
else
Copy corresponding row in Table1 to the bottom of Table2

Sub CompareData()

Dim rngPaste As Range
Dim cell As Range, rngFound As Range
Dim rngSht1ColG As Range, rngSht2ColG As Range
Dim tbl1 As ListObject, tbl2 As ListObject
Dim ws1 As Worksheet, ws2 As Worksheet
Dim wb As Workbook

Set wb = ActiveWorkbook
Set ws1 = wb.Sheets("Sheet1")
Set ws2 = wb.Sheets("Sheet2")

Set tbl1 = ws1.ListObjects("Table1")
Set tbl2 = ws2.ListObjects("Table2")

Set rngSht1ColG = ws1.Range("G2", ws1.Cells(Rows.Count, "G").End(xlUp))
Set rngSht2ColG = ws2.Range("G2", ws2.Cells(Rows.Count, "G").End(xlUp))

For Each cell In rngSht1ColG
    Set rngFound = rngSht2ColG.Find(What:=cell, LookAt:=xlWhole)
    If rngFound Is Nothing Then
        Set rngPaste = tbl2.ListRows.Add.Range
        tbl1.ListRows(cell.Row).Range.Copy rngPaste
    Else
        ws1.Range("M" & cell.Row).Copy ws2.Range("M" & cell.Row)
    End If
Next

End Sub

 

[Amarben]

Not sure if this is correct
You have
1) Table1 in Sheet1
2) Table2 in Sheet2
3) You have data in column G in Sheet1 and Sheet2
4) You have data in column M in Sheet1 and Sheet2

The code will
Loop from G2 down in Sheet1 and check if the value exist column G in Sheet2
If exist copy value Sheet1 column M in corresponding row to Sheet2 column M in corresponding row
else
Copy corresponding row in Table1 to the bottom of Table2

Sub CompareData()

Dim rngPaste As Range
Dim cell As Range, rngFound As Range
Dim rngSht1ColG As Range, rngSht2ColG As Range
Dim tbl1 As ListObject, tbl2 As ListObject
Dim ws1 As Worksheet, ws2 As Worksheet
Dim wb As Workbook

Set wb = ActiveWorkbook
Set ws1 = wb.Sheets("Sheet1")
Set ws2 = wb.Sheets("Sheet2")

Set tbl1 = ws1.ListObjects("Table1")
Set tbl2 = ws2.ListObjects("Table2")

Set rngSht1ColG = ws1.Range("G2", ws1.Cells(Rows.Count, "G").End(xlUp))
Set rngSht2ColG = ws2.Range("G2", ws2.Cells(Rows.Count, "G").End(xlUp))

For Each cell In rngSht1ColG
    Set rngFound = rngSht2ColG.Find(What:=cell, LookAt:=xlWhole)
    If rngFound Is Nothing Then
        Set rngPaste = tbl2.ListRows.Add.Range
        tbl1.ListRows(cell.Row).Range.Copy rngPaste
    Else
        ws1.Range("M" & cell.Row).Copy ws2.Range("M" & cell.Row)
    End If
Next

End Sub

thank you for your quick reply.
it works, but there's a little problem, the copy macro paste the values in column M in a random way without being based on the criterion of the base G.
column G in sheet1 and column G in sheet2 contain unique codes (we can say primary key). I want the copy paste process to be based on the value in column G.
If the value of column G5 exists in both sheets, then copy / paste M5 into sheet2, otherwise (if it does not exist) insert a row at the bottom of sheet2.
I hope I am clear this time and I thank you very much for your help

 

[Zot]

Perhaps I did not understand fully what you really meant. Can you provide example before and after?

 

[Amarben]

Perhaps I did not understand fully what you really meant. Can you provide example before and after?

Thank you again for your reaction

the macro must Compare the values of columns C in the 2 sheets and if they are equal: copy/paste the whole row in sheet2

Example: If Sheets(2).cells("C4").Value is equal to Sheets(1).cells("C7").Value then copy/paste data from row 4 of sheet2 (Source) into row 7 of sheet 1 (Destination).

If a value in column C of sheet2 does not exist in sheet1 THEN add a new compatible row at the bottom in sheet1 (add the row of the value that does not exist in sheet1).

Below is a visual representation of what i'm looking for:

That's Sheet1 (SOURCE), Culumn C (Project Code is a unique value)


And This is Sheet2 (Destination), with the same culumns and format



After Running the Macro, the rows of Sheet1 (Destination) having the same values as column C (Project CODE) of Sheet2 (Source) will be updated (values replaced). If a value of Column C (Project code) does not exist in sheet1, then add a whole row at the bottom / As in the example below



I have been struggling with this task for a few days, I tried some manipulation and I didn't find the right solution, I would be grateful for your help

 

[Zot]

Okay. Now where is column M where you mentioned is causing problem in this example? How it is suppose to result?