Data=Filter=Advanced filter has the option of getting a list of unique
values.

It is also supported by VBA code.

I turned on the macro recorder and executed the actions manually, and got
this:

Sub Macro1()
Range("A1:A56").Select
Application.CutCopyMode = False
Range("A1:A56").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("G1"), _
Unique:=True
Range("G1").Select
Selection.Sort Key1:=Range("G2"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
End Sub

You should be able to generalize it to do what you want. You can copy to
another sheet.

--
Regards,
Tom Ogilvy



"Nikhil" wrote in message
...
I have data in some sheets (the same data can be repeated in different
sheets)..I want to compile distinct records from these worksheets into
another sheet...prefereably using vba code.

I do not want to use a formula...since once i get the distinct data...i
want
to use vlookup to get sales figures and then sort the data on sales...

I also want that the data automatically gets sorted in the compiled sheet
in
desc order.

Plz help.

Nikhil