Filter Unique Entries dynamically

Hi Everyone!

I am trying to filter unique entries from one worksheet to another dynamically. I have written a few VBA macros to do this but I have had no luck.

I've tried things like:

Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

ws1.Range("S7", Range("S7").End(xlDown)).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=ws2.Range("B6" & Range("B65536")).End(xlUp)(3), _
Unique:=True

End Sub


ws1.Range one has the numbers 1,2,3,1,2,3
and my macro returns 1,2,3,1

and I want it to return only the unique entries.

Is it reading the first entry ("1") as the title/criteria and therefore including it?

Any help is much appreciated.

Thanks!
Carol

Hey Ron
Thanks for the tip. I've adapted the code to:

Sub Test()

Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set ws2 = Worksheets("Sheet1")
Set ws1 = Worksheets("Sheet2")

ws2.Range("S7", Range("S7").End(xlDown)).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=ws1.Range("B6" & Range("B65536")).End(xlUp)(3), _
Unique:=True

End Sub

But the first "1" entry is still appearing twice????

Excel considers the first row of an advanced filter source range to be field names. So if your range starts immediately with amounts, the first row is considered the field name.

Also, consequently, if there are no field names in the destination range, Excel will plug the field names from the source data. I'm guessing that your first item is a 1.

So....If you want only the actual unique values, you'll need to either use field names or delete the first item in the destination range.

I hope that helps.

Ron

Hi-
Does anyone know how I can adapt the following so that when it copies to the other worksheet, it only copies the values and not the formatting???

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set ws2 = Worksheets("Sheet2")
Set ws1 = Worksheets("Sheet 1")

FindUniqueValues ws2.Range("S6:S1506"), ws1.Range("B6")
End Sub

Using the function below:

Sub FindUniqueValues(SourceRange As Range, TargetCell As Range)
SourceRange.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=TargetCell, Unique:=True

End Sub

Thanks!
Carol