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