Every now and again I get a problem with cutting and pasting in a table. When I cut a cell from a table and then paste it into the same table it pastes in the value from the cell below the one that was cut. If I cut a cell from outside the table and past it into the table it pastes in the value from the cell below the one that is pasted. The only cure I have found is to restore a backup.
I have no idea what is causing this issue. The table can behave fine for months then suddenly starts acting up.
Has anyone else come across this issue, if so do you know what causes it and how to prevent it?
Any help with this would be much appreciated.
Regards
Chris
Hi Chris,
I've never had that happen. Next time it does, can you see if you can reproduce the problem in a table you can share so we can test it?
Mynda
I was thinking if it might be possible you touched some different Shortkey (other than Ctrl-V for instance) which might causes this? I'm not so familiar with the English shortkeys, so don't know what happens with some of them in the neighbourhood of the Ctrl-V but I think it's something like that. Possible?
Hi Mynda,
I have attached a cut down version of the table that is causing problems. While producing a smaller version I have managed to fix the issue by deleting the rows below the table and then recreating them. I have no idea what causes the problem or why this fixes it. The original, much larger, file contains several other almost identical tables none of which has this issue. This is not the first time this problem has occurred and I now save a backup version of the file every time I close it down but sometimes I have to go back several weeks to find an uncorrupted version.
If you can shed any light on what causes this I would be most grateful.
Thanks
Chris
Hi Chris,
I wasn't able to recreate the issue you describe in the file you shared. Are you able to tell me the steps you're taking in this file so I can reproduce the problem?
Mynda
I was able to replicate the error.
Example:
1) Cell CS6 enter a value eg 999
2) Copy CS6 and paste onto CP3.
3) CP3 value will become >10 (the value of CP4) instead of 999
I have managed to track the problem to two issues but have no idea at this moment why they are causing the problem.
Step 1 : Remove the Conditional Formatting by deleting the rule (not just from the table) as the range covered include cells outside of the table.
Step 2 : From the Formulas - Name Manager option, delete the named range Selected_Data. There are 2 such names but the one seems to be causing the problem is the one without the #REF! error.
Once you have done the above 2 steps, it seems to be OK.
Both steps must be done otherwise it won't solve the problem.
I would also suggest that you delete all the name ranges with errors.
Hope this helps.
Sunny
Thanks Sunny,
That does indeed fix the problem. This is one small part of one of the tables in the worksheet. I will go through the other tables and make the same changes to reduce the chances of it occurring again.
Thanks for your help,
Chris
Well done, Sunny. I tried a few cells to copy, cut and paste but didn't get the error.
Mynda
Thanks Mynda
I think Chris have "discovered" a pretty cool trick and it is very impressive (thanks Chris)
Somehow by deleting the entire row 46 also seems to resolve the issue.
Sunny
Thanks Sunny,
It is always nice to find a new "feature". We just need to find a use for it now.
Chris
