Your " Range("C65536").End(xlUp)) " is unmodified so it always refers to the
active sheet

With ThisWorkbook.Worksheets("Report")
Cuniquecount = CountUniqueValues(.Range(("c2"),
..Range("c65536").End(xlUp)))
duniquecount = CountUniqueValues(.Range(("d2"),
..Range("d65536").End(xlUp)))
euniquecount = CountUniqueValues(.Range(("e2"),
..Range("e65536").End(xlUp)))
End With


--
Jim
"Raj" wrote in message
...
| Hi,
|
| Please help me with the following code:
|
| Sub TestforUniqueness()
| Dim Cuniquecount As Long
| Dim duniquecount As Long
| Dim euniquecount As Long
| Cuniquecount =
| CountUniqueValues(ThisWorkbook.Worksheets("Report" ).Range(("c2"),
| Range("c65536").End(xlUp)))
| duniquecount =
| CountUniqueValues(ThisWorkbook.Worksheets("Report" ).Range(("d2"),
| Range("d65536").End(xlUp)))
| euniquecount =
| CountUniqueValues(ThisWorkbook.Worksheets("Report" ).Range(("e2"),
| Range("e65536").End(xlUp)))
| MsgBox Cuniquecount
| MsgBox duniquecount
| MsgBox euniquecount
| End Sub
|
| The workbook has two sheets: "Summary" and "Reports"
| When the following code is run and the Active sheet is other than
| Reports, it gives an application or object defined error. If add the
| line "Worksheets("Reports").activate" then the code works well. Why
| does this happen? Any way of doing this without activating the
| worksheet.? (Before running this sub the earlier code writes some
| values to the "Summary" sheet. But the summary sheet has not been
| activated in the code before writing those values.
|
| Thanks in advance for the help.
|
| Regards,
| Raj