VBA Compare values - Add row - Paste value

[NathanUU]

Hi people from the internet!

I'm trying to find a solution through VBA for the following dummy situation; there are two workbooks (Table A & Table B) and both contain product information. Table A has it's data provided through SQL and will be the 'master' file. Table B has to automatically get the new products inserted from A.

- So first compare Column A from Table A with Column A from Table B
- If the value from Table A is not in Table B
- Add a row
- Paste value from Table A in Table B (only Product is ok)

Table A

Product	Description
ProductXY	Lorem ipsumXY
ProductXX	Lorem ipsumXX
ProductYY	Lorem ipsumYY

Table B

Product	Description
ProductXY	Lorem ipsumXY
ProductYY	Lorem ipsumXX

Note:
This stuation is part of a bigger automation project and thus i'm limited in options for certain parts of the workflow.
The Excel files are inteded with column headers.
I know you can insert formulas to refer to cells from different workbooks but that approach doesn't provide all the solutions

Thanks for any help/suggestions/code My bad for not the best english here and there.

Kind regards,

Nathan

 

[NathanUU]

I managed to get a working version
Just wondering how i could manage the opposite. That if TextBlockTable has a product that's not in the 'Masterfile' EmpowerTable; the row in TextBlockTable gets deleted. Any suggestions?

Thanks!

Sub Test()
    
    'Declaration Sheets
    Set EmpowerTable = Sheets("Sheet1")
    Set TextBlockTable = Sheets("Sheet2")

    'Setting up ranges
    Set RangeEmpower = EmpowerTable.Range("A2:A10")
    Set RangeTextBlock = TextBlockTable.Range("A2:A10")

    'Disabling screen updates
    Application.ScreenUpdating = False

    'End sub if Ranges are empty
    If RangeEmpower Is Nothing Or RangeTextBlock Is Nothing Then Exit Sub

    'Getting count of Rows
    RowCountEmpower = RangeEmpower.Rows.Count
    
    'Loop the RangeEmpower
    For i = 1 To RowCountEmpower

        'Compare cell values from both ranges
        CellValue1 = RangeEmpower.Cells(i, 1)
        CellValue2 = RangeTextBlock.Cells(i, 1)
        
        'Comparing value of cell from both ranges, column by column
        If CellValue1 <> CellValue2 Then
        Rows(i + 1).Insert
        Range("A" & i + 1).Value = CellValue1
        Exit For
        
        End If

    Next i

    'Enabling screen updates
    Application.ScreenUpdating = True
End Sub