Macro to copy distinct values from one Excel sheet to another

macrosmicrosoft excelvba

I have an Excel workbook that has the following sheets, "Main" and "Count":
Now the "Main" sheet has the column B with list of IDs.

Is it possible to have a macro on "Count" page to copy all distinct values in column A from column B in Main Sheet?

I know the Index function and Advanced filter will work, but I specifically need a simple macro.

Best Answer

Try this:

Sub CopyUnique()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Main")
    Set s2 = Sheets("Count")
    s1.Range("B:B").Copy s2.Range("A1")
    s2.Range("A:A").RemoveDuplicates Columns:=1, Header:=xlNo
End Sub
Related Question