Compare Sheets Find Missing Data

It will be easy this way - everytime something changes in the master sheet, you clear the contents of sheet 2 and re-update.

Will this work for you?

Hi SuperMan

I have a sense of what your after but I'd like to see samples of your worksheets (Master and Sheet2). I'd think a Find based on Concatenation would work here.

Hi ClarkKent88

Do you not have any code that does Find or Lookup in your current file? Do you not have any Drop Downs that allow you to select, for example Equip#?

Is your Sample File a true representation of what you currently have? If it is we have a bit of a task ahead of us...not insurmountable...but more difficult and more coding required.

Hi ClarkKent88

Try this code...I've tested in both 2003 and 2007...let me know of issues

Public Sub NewEntWhole()
    Dim loM As ListObject, lo2 As ListObject
    Dim TblMData As Variant
    Dim iM As Long
    Dim dDate As Date
    Dim lDate As Long
    Dim rng As Range
    Dim ct As Variant
    Dim shM As Worksheet
    Dim sh2 As Worksheet
    Dim hdM As Integer
    Dim aCell As Range

    hdM = 0    'rows above table M
    Set shM = Sheets(1)
    Set sh2 = Sheets(2)
    Set loM = Sheets(1).ListObjects(1)
    Set lo2 = Sheets(2).ListObjects(1)

    With loM
        TblMData = .DataBodyRange
    End With

    For iM = 2 To UBound(TblMData, 1) + 1
        sh2.Activate
        With lo2
            .Range.AutoFilter Field:=1, Criteria1:=loM.Range(iM, 1).Value
            .Range.AutoFilter Field:=2, Criteria1:=loM.Range(iM, 2).Value

            If IsDate(loM.Range(iM, 4)) Then
                sDate = loM.Range(iM, 4)
                dDate = DateSerial(Year(sDate), Month(sDate), Day(sDate))
                lDate = dDate
                .Range.AutoFilter Field:=4, Criteria1:=">=" & lDate, Operator:=xlAnd, Criteria2:="<" & lDate + 1
            Else
                .Range.AutoFilter Field:=4, Criteria1:=loM.Range(iM, 4).Value
            End If
        End With

        Select Case Val(Application.Version)

        Case Is <= 11
            Set aCell = sh2.Range("D1")
            aCell.Select
            With aCell.ListObject.ListColumns(4).Range
                ct = .Columns(4).SpecialCells(xlCellTypeVisible).Cells.Count - 2
            End With

            If ct <= 0 And loM.Range(iM, 1).Value > 0 Then
                shM.Activate
                shM.Range(Cells((iM + hdM), 1), Cells((iM + hdM), 7)).Copy

                sh2.Activate
                NextRow = Range("B65536").End(xlUp).Row
                Range("A" & NextRow).Select
                ActiveSheet.Paste
            End If

        Case Is >= 12
            Set rng = lo2.AutoFilter.Range
            ct = rng.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
            If ct = 0 And loM.Range(iM, 1).Value > 0 Then
                shM.Activate
                shM.Range(Cells((iM + hdM), 1), Cells((iM + hdM), 7)).Copy

                sh2.Activate
                NextRow = Range("B65536").End(xlUp).Row + 1
                Range("A" & NextRow).Select
                ActiveSheet.Paste
            End If
        End Select

        With lo2
            .Range.AutoFilter Field:=1
            .Range.AutoFilter Field:=2
            .Range.AutoFilter Field:=4
        End With
    Next
    shM.Activate
End Sub

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.