VBA Copy list from filter and pasting into a column

[chowee21]

I have columns on Sheet 1 with a filters and I want a way to make a list on Sheet 2 of each item (with out repeating) that is in the corresponding column on sheet 1.

This will be programmed in a userform to create the list when the form is loaded.

The end result being this:
CopyFilterList.png

This is an example and there could be up to 50 or more options.

Thanks!

[chowee21]

anyone have any ideas for me??

[Alf]

You could try a macro like this.

Please Login or Register  to view this content.

Alf

[chowee21]

You could try a macro like this.

Please Login or Register  to view this content.

Alf

Close... but it will return a single item multiple times and will go into an error if you try to run it twice. I need it to, on some action (like making a sheet active), automatically update the list on sheet two to the most recent list. So for example, if I add "Coffee" to column C of sheet 1, then when i make sheet 2 active, it will add "Coffee" to list 3 in column C.

Thanks!

[Alf]

but it will return a single item multiple times

??????

No the "AdvancedFilter" "True" will only retun singel uniqe items but if you have "slopy" data with trailing spaces i.e. "Apples" and "Apples " then it will give you both "Apples" and "Apples " as they both are unique.

To run the macro multiple times you need to clear sheet2 so adding these lines will fix this problem.

Please Login or Register  to view this content.

Alf