Macro that searches column B in all sheets for all the different values and return in list

I have a macro that looks for all the different values in column B and returns the different values only once in a list. I am trying to get this macro to do the same thing but to look in all the sheets column B and return all the different values onto on sheet. Here is the code I'm using to do it with one sheet.

Please Login or Register  to view this content.

Thank you

You can loop through all sheets, collecting the data the same way (unfortunately, as far as I remember advanced filter output has to go to the same sheet as data) and copying into "final sheet" then you can do the same in the final sheet.

Other option (if total number of used rows in do not exceed excel limits) you can copy all data into "final sheet" and then deduplicate it there.

anyway you will need a loop through all sheets skipping the one where output shall be placed, so something like:

Please Login or Register  to view this content.

Try rearanging commands (last row changes after something is copied into the sheet)

Please Login or Register  to view this content.