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