How to avoid duplicates across multiple worsheets in excel?

You can use the following macro linked to the Workbook_SheetChange event on the ThisWorkbook tab.

It's a bit slow and can be improved by restricting the search range to the cells that you are likely to use.

e.g. Sheet.Range(Sheet.Cells(1, 1), Sheet.Cells(100, 100)) instead of Sheet.Cells



Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim DuplicatedValue As Boolean
DuplicatedValue = False
For Each Sheet In Sheets
If Sheet Is Sh Then
If Application.CountIf(Sheet.Cells, Target) > 1 Then
DuplicatedValue = True
End If
Else
If Application.CountIf(Sheet.Cells, Target) > 0 Then
DuplicatedValue = True
End If
End If
Next Sheet
If DuplicatedValue = True Then MsgBox "Duplicate"

End Sub