VBA Copy and Paste Values

Hi all,
I have a small amount of code copying a coulmn and pasting the unique values in another part of the sheet; however it is copying the formulas. I have tried to incorporate "PasteSpecial xlPasteValues" into my code but have not been able to get it working.

Any help is greatly appreciated, please see below for code.

thanks

Leigh

Please Login or Register  to view this content.

Hi,

an easy Excel basics way to extract unique values is to use an advanced filter and thus whatever manually or under VBA …
As it seems you are under Excel 365 version you have the worksheet function UNIQUE.

Hi Jeff,
Thanks for the reply, I did try this initially, it gives me a Compile Error: Expected: end of statement

thanks
Leigh

Hi Marc,
I have used UNIQUE to extract distinct values before but have not had success using it to extract all unique values, I.E copy all values and remove any duplicates.

Have you used this function in this way?

thanks

Leigh

Ahh thank you Jeff, that has worked perfectly!!!!

Appreciate the support!!

As I'm not under 365 version but according to many theads using this function it well works if the logic is respected.

If you follow the top page yellow banner in order to attach a sample workbook I'm sure Jeff will help you for 365 ways
like me with beginner level Excel basics 'cause, as a reminder, an easy advanced filter just needs a single codeline
like you can operate yourself just activating the Macro Recorder …

Thanks Marc, I always like to try and work it out first so will actually see if that's an easier way of doing it

Also thanks for your support!!

Leigh

Hi both,
just to follow up on this thread, Jeff your solution did work very well but as Marc suggested to try Unique, I tried to see if this was an easier option. I have successfully now got the formula to the way I had it working with the macro and of course this now runs without the need of initiating afore mentioned macro.

For anyone coming across this thread the formula to return all distinct value from column D into your selected output column is as follows:

Please Login or Register  to view this content.

Again thanks both for the support and happy to give reputation points for you both!!