Comparing two sheets and adding in new data?

[afc171]

Hi all,
I am trying to compare two sheets and copy the new data from 1 to another if its not there, this code only seems to copy the first 2 columns.
What do I need to change to include up to column I? Also is there a way to copy them to Compare sheet with a different colour to see what has been added?

Sub Compare()
   Dim Ary As Variant
   Dim i As Long, j As Long
   
   Ary = Sheets("RECP1").Range("A1").CurrentRegion.Value2
   With Sheets("Compare")
      j = .Range("A" & Rows.Count).End(xlUp).Row
      For i = UBound(Ary) To 2 Step -1
         If .Cells(j, 1).Value = Ary(i, 1) Then
            j = j - 1
         Else
            Rows(j + 1).Insert
            .Cells(j + 1, 1).Resize(, 2).Value = Array(Ary(i, 1), Ary(i, 2))
         End If
      Next i
   End With
End Sub

Thank you

 

[Alex Blakenburg]

I don't have any data to test this with but see if this does what you need:
The code in red are the changes

Sub Compare()
   Dim Ary As Variant
   Dim i As Long, j As Long, zCol As Long
   
   Ary = Sheets("RECP1").Range("A1").CurrentRegion.Value2
   With Sheets("Compare")
      j = .Range("A" & Rows.Count).End(xlUp).Row
      For i = UBound(Ary) To 2 Step -1
         If .Cells(j, 1).Value = Ary(i, 1) Then
            j = j - 1
         Else
            Rows(j + 1).Insert
            For zCol = 1 To Columns("I").Column
                .Cells(j + 1, zCol).Value = Ary(i, zCol)
            Next zCol
            .Range(.Cells(j + 1, "A"), .Cells(j + 1, "I")).Interior.Color = vbYellow
         End If
      Next i
   End With
End Sub

 

[afc171]

Thanks Alex, works great

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.

 

[afc171]

Alex can that code be modded to do it on 1 sheet...for example insert new rows into L:T and highlight if not in A:I ?
so its easier to see the differences. Thanks

Consumables Mastersheet V2.6 LC.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	product	description	lot_number	to_bin_number	movement_quantity	expiry_date	transaction_type	dated	WHS			product	description	lot_number	to_bin_number	movement_quantity	expiry_date	transaction_type	dated	WHS
2	CHE0033	SODIUM PYRUVATE	2511572	LCLAB28	45	31/Jul/23	RECP	03/Oct/22	LC			CHE0033	SODIUM PYRUVATE	2511572	LCLAB28	45	31/Jul/23	RECP	03/Oct/22	LC
3	GAS0003	LIQUID NITROGEN	031022	SHED	568		RECP	03/Oct/22	LC			GAS0003	LIQUID NITROGEN	031022	SHED	568		RECP	03/Oct/22	LC
4	PRI0027	Black Ink Cartridge	031022	LC26	4		RECP	03/Oct/22	LC
5	CHE0030	L-GLUTAMINE 200MM	2412068	FC	4	31/Jan/24	RECP	04/Oct/22	LC			CHE0030	L-GLUTAMINE 200MM	2412068	FC	4	31/Jan/24	RECP	04/Oct/22	LC
6	PKG0007	MEDIUM DRY ICE BOX	041022	LC/P8	60		RECP	04/Oct/22	LC			PKG0007	MEDIUM DRY ICE BOX	041022	LC/P8	60		RECP	04/Oct/22	LC
7	PKG0028	VIAL BOX - M-410C	041022	LC/P8	360		RECP	04/Oct/22	LC
8	CHE0029	NON ESS AMINO ACIDS	2512133	LCLAB28	45	28/Feb/24	RECP	05/Oct/22	LC			CHE0029	NON ESS AMINO ACIDS	2512133	LCLAB28	45	28/Feb/24	RECP	05/Oct/22	LC
9	CON0010	175CM TC FLASKS	2091013	LC25	2	29/Mar/27	RECP	05/Oct/22	LC			CON0010	175CM TC FLASKS	2091013	LC25	2	29/Mar/27	RECP	05/Oct/22	LC
Sheet1

 

[Alex Blakenburg]

I have logged off for the night but if you are using excel tables give me the table names.

 

[afc171]

They are not in tables but if its easier, Table15 (left) and Table16 (right)

 

[afc171]

Hi @Alex Blakenburg did you get a chance to look at comparing on the same sheet? thanks

 

[Alex Blakenburg]

Oops thanks for reminding me.
Your previous response indicated that you were not in fact using tables (I just thought the line colouring indicated you were).

If you are not using tables try the code below.

Sub Compare()
    Dim Ary As Variant
    Dim i As Long, j As Long, zCol As Long
    Dim shtData As Worksheet
    Dim lrRECP1 As Long, lrComp As Long
    Dim rngRECP1 As Range, rngComp As Range
    
    Set shtData = ActiveSheet
    
    With shtData
        lrRECP1 = .Range("A" & Rows.Count).End(xlUp).Row
        Set rngRECP1 = .Range(.Cells(1, "A"), .Cells(lrRECP1, "I"))
        lrComp = .Range("L" & Rows.Count).End(xlUp).Row
        Set rngComp = .Range(.Cells(1, "L"), .Cells(lrComp, "T"))
    End With
    
    Ary = rngRECP1.Value2
    With rngComp
        j = lrComp
        For i = UBound(Ary) To 2 Step -1
            If .Cells(j, 1).Value = Ary(i, 1) Then
                j = j - 1
            Else
                .Rows(j + 1).Insert Shift:=xlDown
                For zCol = 1 To Columns("I").Column
                    .Cells(j + 1, zCol).Value = Ary(i, zCol)
                Next zCol
                .Rows(j + 1).Interior.Color = vbYellow
            End If
        Next i
    End With
End Sub

 

[afc171]

thanks mate, I might put it in a tab might be easier to sort out. I will try that code.