VBA to compare master table to subordinate datasheets and update values

[Eric111]

Greetings Excel Masters,

Any assistance on this project would be greatly appreciated.

The macro that I am trying to build would compare the following –

Column A on (“Emp Info”) = Master Sheet
Column A on (“Emp Data Production”)
Column A on (“Emp Data Quality”)
Column A on (“Emp Data PHI”)
Column A on (“Emp Data PQL Errors”)

If column A on (“Emp Info”) has a value that is not on either of the other sheets, I need the macro to add the Values from Columns A,B,C to the next blank row on the deficient sheet(s).

If column A on (“Emp Info”) does not have a value that is on any of the other sheets, I need the macro to remove the Value and Clear the row.

Thanks in advance for your assistance,

[Eric111]

This code compares Column A on (“Emp Info”) = Master Sheet with Column A on (“Emp Data Production”) if the data does not exist it deletes the row in (“Emp Data Production”). This can be adapted to include the other sheets.


Sub Test1()

Dim LastRow As Long
Dim ThisRow As Long
Dim Range2 As Range

Sheets("Emp Info").Select
' Range2 is the target range which is checked for matching value
Set Range2 = Range(Range("A2"), Range("C65536").End(xlUp))
Sheets("Emp Data Production").Select
LastRow = Range("C65536").End(xlUp).Row
' The original range is looped and rows which do not contain a march are deleted
For ThisRow = LastRow To 2 Step -1
If Application.WorksheetFunction.CountIf(Range2, Range("A" & ThisRow).Value) = 0 Then Range("A" & ThisRow).EntireRow.Delete
Next ThisRow

End Sub


Any help on the following would be appreciated...

If a new value is added to Column A on (“Emp Info”) = Master Sheet, I need the macro to add the value of columns "A:C"(“Emp Info”) to "A:C" (“Emp Data Production”) first empty row.


Thanks for your assistance.