SOLVED

Filtered List - Cant Copy from one column to another

Super Contributor

Hello Experts, I have had this issue of not being able to copy data from one column to another if the list is filtered. I want to be able to copy the entire range and paste it into the same rows in the other column but excel doesnt allow it.  I have to do it one by one and its time consuming.  

 

Is there some trick to doing this? 

 

thank you.  

 

Tony2021_1-1668088058055.png

 

 

10 Replies
best response confirmed by Tony2021 (Super Contributor)
Solution

@Tony2021 

 

Flash Fill. In the gif, I use the menu command, but you could also do the shortcut ctrl+e.

 

FlashFill.gif

@Patrick2788 

ahh yes the flash fill.  Awesome.  Thank you very much.  

You're welcome! The only drawback to using Flash Fill is the columns have to be neighboring each other. In your example it works out.

@Patrick2788 

HI Patrick, I am having a tough time making it work. My columns are next to eachother in the attached but I still cant get flash fill to work. Do you happen to see why?

In my real data set the columns are actually not next to eachother. Do you know of a work around?  I could put the columns next to each other I guess since it would still be better than manually copying and pasting one by one. 

 

thank you
Please see attached

@Tony2021 

Thank you for the followup and the sample workbook.

 

I was able to reproduce the same error you received. That particular error seems to be nowhere to be found online so I'll post it here in the hope it may come up when someone searches for it:

 

We didn't fill in values because the display formatting for some of the cells in the active column is using a different level of precision than the underlying value

 

The part Flash Fill doesn't like is the large decimal (7 numbers right of the decimal).  Through some trial and error, I got it to work but only when I truncated the decimal down to 1.  There's no issue with the cell formatting - it's all about the value in the formula bar.  I've attached my sample workbook.

Amazing you figured out how to make it work. I unfortunately can not use flash fill since my columns are not next to each other. darn! I initially used TRUNC as 2 decimals to test if it will work with 2 decimals and it wont. You have to use 1 decimal. Pretty finicky. thanks again for the help. Really appreciate it.

@Tony2021 

To copy-paste from visible cells to visible cells, try using “Sub CopyVisibleToVisible1” macro. You can put the macro in Personal.xlsb & assign the macro to a toolbar button, so you can use it on any open workbook.
You can find it here:
https://techcommunity.microsoft.com/t5/excel/paste-to-visible-cells-only-in-a-filtered-cells-only/m-...

Hi Cangkir, wow! that worked perfectly! Thank you so much for that code. I put it in the personal.xlsb. I am glad you mentioned the personal.xlsb since I forgot about it since I have it hidden on start and thought I would have to save my file as a macro enabled but I dont need to now. thanks again.

From what I've read, Flash Fill uses "bits of code" to recognize patterns and fill out lists. However the feature is coded it must not be written for a decimal of that size.

You're welcome, glad it work.