Hi everyone,

XLD has quickly identified a typo which prevented the code from running. I declared Cl as range but I wrongly refer this variable as Cell in the For loop. The codes works perfectly without expression Option Explicit.

Johnske, your suggestion is noted & thanks. However, the expression wks.Range(wks.Range("A3"), wks.Range("A3").End(xlDown)) is more dynamic in terms of variable ranges in sheets.

Norie, you're right - nothing wrong with the variable object being declared as range. I use the countif is to copy only unique entries to sheet1. In other words, when an entry has not been copied to sheet1 before, the countif function returns 0 and the code proceeds to copying.

The working code is as follows:

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("A3").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
Your inputs are greatly appreciated. Have a nice weekend.

Best regards


KP