In the "master" file, select the master sheet and right click on it to VIEW
CODE.
Press F4 to View the Properties Window
You should see Sheet1(Sheet1), etc.
The value in the Paren's is the sheet name that you've given it. The value
before the parens if the Worksheet code name.
In the Properties window, change (Name) to Master.
Now Insert a Module
In that module paste the following:
Sub FindDuplicates()
Dim aWB As Workbook
Dim aWS As Worksheet
Dim WS As Worksheet
Dim oWB As Workbook
Dim oWS As Worksheet
Dim lRow As Long
Dim lCol As Long
Dim myCol As Long
Dim myRow As Long
Dim myEmployee As Range
Set aWB = ActiveWorkbook
For Each WS In aWB.Worksheets
If WS.CodeName = "Master" Then
Set aWS = WS
Exit For
End If
Next WS
Set WS = Nothing
If aWS Is Nothing Then
MsgBox ("The worksheet with code name Master does not exist in the " &
vbNewLine & _
"active workbook")
End If
'Determine last row of data in master workbook (in column 1)
lRow = aWS.Cells(aWS.Rows.Count, 1).End(xlUp).Row
'Determine last column of data in master workbook Row 2
lCol = aWS.Cells(2, aWS.Columns.Count).End(xlToLeft).Column
For myRow = 3 To lRow
Set myEmployee = aWS.Cells(myRow, 1)
If Not IsEmpty(myEmployee) Then
If LCase(myEmployee.Value) <> "jr" And _
LCase(myEmployee.Value) <> "sr" Then
For myCol = 2 To lCol
Debug.Print myEmployee.Value, aWS.Cells(2, myCol).Value,
aWS.Cells(myRow, myCol).Value
Next myCol
End If
End If
Next myRow
End Sub
That's a start to getting what you want.
