Automatic Duplication of Unique Values in Separate Worksheet?

[mollys]

Hi Mr Excel board -
Here's what I'm trying to do.

I have a template that I'm using. When I open the template the first thing I do is paste in a long list of vendors. This list will have duplicates in it.

On a separate worksheet I then copy and paste the vendor list and remove duplicates.

Basically I want to automate this process so when I paste in the initial vendor list, the other worksheets will *automatically* populate with a deduplicated list.

I'm sure there's a way to do this with Macros, but I'm still learning my way around Excel.
Any help would be TRULY appreciated.

Thanks!!
Molly