Auto copy a row to another worksheet, then check the sheets for duplicates

[Tigger118]

Hi All,

I have an excel spreadsheet, which has a main database sheet. Within columns A & B, "Status" & "Sector" there are drop down menus and these can be changed. I have the following macro, which copies the row to another named sheet as and when these columns are changed, but I need a macro which will then check all the sheets, except the main database and the one which it just copied into for duplicates i.e.:

Joe Bloggs row - if I change this status from Active to Deactive then his details should just show on 3 worksheets - the main database the Deactive and then whatever his sector is.

Same applies if I change his sector from Public to Private, his details will show in main database, Private and whatever his status is.

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if Change was made to Column A or Column B
If Target.Column = 1 Or Target.Column = 2 Then
'If True, then determine the next available Row in Column A of Target sheet
nxtRw = Sheets(Target.Value).Range("A" & Rows.Count).End(xlUp).Row + 1
'Copy Row from Main sheet to Target sheet
Range(Target.Address).EntireRow.Copy _
Destination:=Sheets(Target.Value).Range("A" & nxtRw)
End If
End Sub

Many thanks in advance.