I am trying to capture changes for employees week to week.
Changes like New employees and changes in Address, Phone Numbers, emails...etc
My data is found A-H.
I am comparing a file every week. Sheet1 is the Master file, sheet2 is the weekly report that contains all the information on Sheet1 plus any new changes. The items not found on Sheet1 are then sent to Sheet3 as the CHANGES.
The code below is giving me the names not found on the master file and so those are the NEW employees.
What i need now is code that will also look at say column E, Column F etc...Even though an employee is existing they made changes to personal data. I need to have those employees also go to sheet3.
Any suggestions would be appreciated.
Sub NewTermsRpt()
Dim vDataOne, vDataTwo, vNotFound
Dim j As Long
Dim k As Long
Dim sSearch As String
Dim sData As String
With Sheets("Sheet1")
vDataOne = .Range("a1:h" & .Range("h65536").End(xlUp).Row)
End With
With Sheets("Sheet2")
vDataTwo = .Range("a1:h" & .Range("h65536").End(xlUp).Row)
End With
vNotFound = vDataTwo
For j = 1 To UBound(vDataTwo, 1)
sSearch = vDataTwo(j, 1) & vDataTwo(j, 2)
For k = 1 To UBound(vDataOne, 1)
sData = vDataOne(k, 1) & vDataOne(k, 2)
If InStr(sData, sSearch) Then
vNotFound(j, 1) = ""
vNotFound(j, 2) = ""
vNotFound(j, 3) = ""
vNotFound(j, 4) = ""
vNotFound(j, 5) = ""
vNotFound(j, 6) = ""
vNotFound(j, 7) = ""
vNotFound(j, 8) = ""
End If
Next k
Next j
With Sheets("sheet3")
.Activate
.Range(Cells(1, 1), Cells(UBound(vNotFound, 1), 8)).Select
Selection = vNotFound
Selection.Sort .Range("a1")
.Range("a1").Select
End With
End Sub
Changes like New employees and changes in Address, Phone Numbers, emails...etc
My data is found A-H.
I am comparing a file every week. Sheet1 is the Master file, sheet2 is the weekly report that contains all the information on Sheet1 plus any new changes. The items not found on Sheet1 are then sent to Sheet3 as the CHANGES.
The code below is giving me the names not found on the master file and so those are the NEW employees.
What i need now is code that will also look at say column E, Column F etc...Even though an employee is existing they made changes to personal data. I need to have those employees also go to sheet3.
Any suggestions would be appreciated.
Sub NewTermsRpt()
Dim vDataOne, vDataTwo, vNotFound
Dim j As Long
Dim k As Long
Dim sSearch As String
Dim sData As String
With Sheets("Sheet1")
vDataOne = .Range("a1:h" & .Range("h65536").End(xlUp).Row)
End With
With Sheets("Sheet2")
vDataTwo = .Range("a1:h" & .Range("h65536").End(xlUp).Row)
End With
vNotFound = vDataTwo
For j = 1 To UBound(vDataTwo, 1)
sSearch = vDataTwo(j, 1) & vDataTwo(j, 2)
For k = 1 To UBound(vDataOne, 1)
sData = vDataOne(k, 1) & vDataOne(k, 2)
If InStr(sData, sSearch) Then
vNotFound(j, 1) = ""
vNotFound(j, 2) = ""
vNotFound(j, 3) = ""
vNotFound(j, 4) = ""
vNotFound(j, 5) = ""
vNotFound(j, 6) = ""
vNotFound(j, 7) = ""
vNotFound(j, 8) = ""
End If
Next k
Next j
With Sheets("sheet3")
.Activate
.Range(Cells(1, 1), Cells(UBound(vNotFound, 1), 8)).Select
Selection = vNotFound
Selection.Sort .Range("a1")
.Range("a1").Select
End With
End Sub