How to not overwrite cells in filtered table using copy and paste values

[Victor107200]

Hi,

I have search for an answer to this question without satisfactory results. I hope someone here at the forum can help me out. 

The problem is that sometimes when I copy and past a value from one cell into many cells in a filtered table, the value also gets copied into invisible cells. I have found that it may have to do with the selected pasting option. Because it seems as if the problem only occurs (but I am not sure) if I copy and paste “values” (or formulas, or any other pasting option other than the default paste option). Is there any way to make sure that Excel only copies and past even formulas or values into visible cells?

Example.

Pic 1 - In the table below I will filter column A and only see the rows with “Jan”.  

Pic 2 - I then write “ok” in the first cell and copy and paste (using keyboard shortcuts, ctrl c, ctrl + spacebar and then ctrl v) that into all the rows matching the value “Jan”.

Pic 3 – The result is fine.

Pic 4 – I did the same thing but copy and pasted “values” into all visible cells instead of using keyboard shortcuts. The result is as you can see that all cells got the “ok”.

Thanks in advance for any help given.

Brgs,

Victor

[#2]

@Victor107200Hello Victor, I have tried to reproduce your scenario. In both cases, only the visible cells were described with oK. I have tested under Excel for the Web. Which operating system and which Excel version are you working with? That would certainly be interesting for other testers.

[#3]

Hello, Thanks for your reply.
MicrosoftS Excel Microsoft 365 MSO (Version 2209 Build 16.0.15629.20200) 64-bit
Windows 10 Pro, Version 21H2, OS-version 19044.2130

I also reproduced it now in Excel for the web and was suprised to see that as you say, only the visable cells gets "ok" copied to them. Very strange.

[#4]

@Victor107200 

Do I do anything differently from you? See attached video!

Exel for the web: Build 16.0.15811.35904

[#5]

@Victor107200 

You may wish to review how this has been discussed elsewhere , and if you page + scroll to the responses by member Cangkir, how to resolve it with a procedure/macro.

[#6]

Thanks but I tried the solution and it did not work for me. Also, even if it did, it seems cumbersome to use a macro as a workaround for something that should be very simple and built in from scratch in Excel.

[#7]

@dscheikey 

Yes and no... When I do it as you do in the video I get the same result. But what I am trying to do is to copy and past values (or formulas, or formatting etc.). Please see my video and you will understand. 

Thanks for taking the time to do a video. Appreciate it!

[#8]

@Victor107200 

I am familiar with this bug. I am a consultant and both me and my clients frequently work in excel.
On my end it seems to happen in regular (non table) cells as well when many users filter rows and use the "only show my own filters" option.