Hi everyone,

The following code is to merge all records from range A3 downwards from all sheets into sheet1. The data to be merged needs to be unique - ie if I have multiple records with string value of "SSSS" in column B in any sheet, only 1 entry is copied into sheet1. The problem I'm having is the code merges everything including duplicates.

The problem, I think, is the variable declaration. Local variable cl is defined a range and I'm trying to do a countif based on a range rather than a value of a range in the for loop. 

Sub Combine()
    Dim cl             As Range
    Dim wks             As Worksheet
 
    For Each wks In ThisWorkbook.Worksheets
        If wks.Name <> "Sheet1" And wks.Range("A3") <> "" Then
            For Each cell In wks.Range(wks.Range("A3"), wks.Range("A5").End(xlDown))
            If WorksheetFunction.CountIf(Worksheets("Master").Range("B:B"), cl) = 0 Then
              cl.EntireRow.Copy Destination:=Worksheets("Sheet1").Range("A65536").End(xlUp).Offset(1, 0)
            End If
            Next cl
        End If
    Next wks
 
End Sub
Any suggestion please. Thanks for your help.

Regards


KP