Compare data between 2 sheet and display the missing items on another sheet

[TRY369]

Hi there! So on this 2 sheet of data, I would like to compare between them and show the missing data onto a new sheet. However, with the code i have, it deletes the data from sheet3. Is there any way I can edit it such that the missing data will be pasted onto a new sheet instead?

Private Sub CommandButton1_Click()
Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim sht3 As Worksheet
Dim c1row As Long
Dim c2row As Long
Dim C2TotalRows As Long
Dim CustID As String
Dim NoDups As Long

Set sht1 = Worksheets("Sheet3")
Set sht2 = Worksheets("Sheet4")
Set sht3 = Worksheets("Sheet5")
sht2.Activate
C2TotalRows = Application.CountA(Range("A:A"))
c1row = 2

Do While sht1.Cells(c1row, 1).Value <> ""
CustID = sht1.Cells(c1row, 1).Value

    For c2row = 2 To C2TotalRows
        
        If CustID = Cells(c2row, 1).Value Then
            sht1.Activate
            Rows(c1row).Delete
            NoDups = NoDups + 1
            c1row = c1row - 1
            sht2.Activate
            
            Exit For
        End If
    Next
    
    c1row = c1row + 1
    
Loop

End Sub

 

[Zot]

Since you are deleting the matching CustID in sht1, didn't what left on sht1 would be the missing data?

Anyway, you do not need to switch sheet by activating back and forth since you have already defined each shee by set statement

sht2.Activate
C2TotalRows = Application.CountA(Range("A:A"))

can be just
C2TotalRows = Application.CountA(sht2.Range("A:A"))

If you do not want to delete any row if matching found, you can add something like this:

If c2row = C2TotalRows Then
     c3row = c3row + 1
     sht1.Range("A" & c1row, "C" & c1row).Copy sht3.Range("A" & c3row)       ' Assuming you have same table layout, I copy the matching row to sheet 3
End If

c3row is adding row to sht3 every time nothing was found

 

[TRY369]

If c2row = C2TotalRows Then c3row = c3row + 1 sht1.Range("A" & c1row, "C" & c1row).Copy sht3.Range("A" & c3row) ' Assuming you have same table layout, I copy the matching row to sheet 3 End If

Hi there, sorry for the late reply. But the code works wonderfully!! Just one more question, when the format is pasted to the sheet3, how do i make i such that it is pasted from the second row onwards?
Also, is it possible to make it such that when i press the "print" button mulitple times, it wont duplicate the with the previous one?

 

[Zot]

Hi there, sorry for the late reply. But the code works wonderfully!! Just one more question, when the format is pasted to the sheet3, how do i make i such that it is pasted from the second row onwards?
Also, is it possible to make it such that when i press the "print" button mulitple times, it wont duplicate the with the previous one?

These were the code I simply wrote before but not really tested them

Private Sub CommandButton1_Click()

Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim sht3 As Worksheet
Dim c1row As Long
Dim c2row As Long
Dim c3row As Long                 ' I added this
Dim C2TotalRows As Long
Dim CustID As String
Dim NoDups As Long

Set sht1 = Worksheets("Sheet3")
Set sht2 = Worksheets("Sheet4")
Set sht3 = Worksheets("Sheet5")
'sht2.Activate                                    ' no need to activate since you've aslready defined sht2, can just use it like below
C2TotalRows = Application.CountA(sht2.Range("A:A"))
c1row = 2
c3row = 1

Do While sht1.Cells(c1row, 1).Value <> ""
CustID = sht1.Cells(c1row, 1).Value

    For c2row = 2 To C2TotalRows
        
        If CustID = sht2.Cells(c2row, 1).Value Then
'            sht1.Activate
            sht1.Rows(c1row).Delete
            NoDups = NoDups + 1                               ' not sure where you use this
            c1row = c1row - 1
'            sht2.Activate
            Exit For
        Else                                                              ' copy ID not found
            If c2row = C2TotalRows Then
                c3row = c3row + 1
                sht1.Range("A" & c1row, "C" & c1row).Copy sht3.Range("A" & c3row)   ' Assuming you have same table layout, I copy the matching row to sheet 3
            End If
        End If
    Next
    c1row = c1row + 1
    
Loop

End Sub

c3row was predefined as 1. SO, when added 1, it will start from two (if this was what you meant?)

I don't understand about pressing "Print" button, a button that as far as I remember I've never used ?