I teach large classes 100+ and this has evolved with each graduate assistant, computer, software change over the past 15 years. Can I do that Masters??? If this doesn't work, your format protection feature doesn't work. I have a huge worksheet with severals sheets… and a mega headache! Today, I find that even drag-and-drop copying is not working as expected. Hi Jan, none of the methods work. We will see in the next version whether any change is made as a result. What we found as a workaround was that selecting the required range, holding Ctrl and dragging the entire frame from workbook A to workbook B successfully copied the range.
Unfortunately you are prohibited from performing such a simple action because the worksheet is protected in a way that you cannot select the cells with data you need. This only affects Excel, I haven't observed the issue in any other app. Looks like this issue is going on since last October, with no resolution offered from Microsoft! If I select a cell that has been highlighted but has no content that also works properly. The fancy formatting with colors, borders, boxes helped to avoid errors with data entry. I could even copy in the failing workbook and paste correctly in another one. They discuss locking cells and protecting worksheets as if it works fine, as if this serious flaw does not exist. I have been dealing with recovered files, multiple saves and so on.
It seems so simple to enforce paste-values-only without needing to rely on macros, but after so many years I've pretty much given up hope. I use a nifty little bit of code to insert the contents of a cell into the footer of the page. You could lose your job. I want to allow users to copy data into the sheet but that when they do it retains the sheets formatting and ignores any formatting that they may have copied from their original sheet. There is actually a third option for copying that lies somewhere between the other two methods by right-clicking the mouse when the selection is highlighted. We can simply select and copy a single cell with the formula or format we need and then select the range we want to paste either to and then paste formula or format, whichever would apply. This is definitely something that has changed or a parameter that I've changed but I don't know what.
There should also be no restriction on users copying data from my sheet to other sources. A copied cell usually have the dashed lines circling it all the time. I tested with different files on three separate machines. The macros I copied count the number of coloured cells in a range. Press Alt+Q to return to the worksheet. I am a complete novice with code so please be specific if you suggest a macro. You plan to copy the raw data and paste it to a new worksheet and do some analyses on your own.
Ctrl+X, Ctrl+V, Shift+Del, Shift+Insert that you don't want used. I could not get it to fail again. This sheet is going to be used by other people that have very basic Excel skills. I did my copying and once the page was working I removed the comment ticks and everything worked. I hope the manual to automatic switch in calculations works for you.
I have selected the options you mentioned when I protected the sheet. Disable cutting from unlocked cells 2. So I opened the clipboard and saw that i had 24 items of the same one in the clipboard. However, it contains hiddent rows and when I copy-paste it, the hidden rows are included. How can I protect the original formatting from such pasting? In 365 they try but from my experience sometimes fails to open all of them in only one application.
I even uninstalled office and reinstalled it. Right clicking also didnt work as the drop down only appeared for less than a second and disappeared again. It used to display a different cursor during this copy operation. Probably quite a patchwork job by now. I am an occasional Excel user: the only thing that was different between the failing workbook and the rest of them is that it contained macros. I find it especially infuriating that this flaw has existed in Excel for decades and yet Microsoft's support website does not mention it at all.
Back to seeking an elegant macro solution but that means macro security issues on the network :- which many organisations are not prepared to do. I've just come across this issue in Excel on a colleague's computer, running Windows10. Thousands of people have been searching for years for a way to prevent pasting in Excel from changing formats without needing to enable macros, and you make it sound like you just stumbled across this new idea that only needs to be suggested to the developers. Now both value columns have the same conditional formatting rules applied. Now you might be able to solve my big question here: I can copy-paste from a protected sheet here. What are you supposed to do then? Since it is protected, I cannot copy only visible row as I cannot access F5… Is there a different way to do so? With Name Box sitting on the top of cell grid; and on the left to formula bar , you may go to any range on s spreadsheet, even if it is protected from selecting.
Hi Jon, I have the exact same problem on both my surface pro 4 and on my freshly formatted desktop pc. I want them to be able to alter the unlocked cells but have the locked cells with formulas stay in their correct columns. I am using Excel 2007. Column D is a locked formula that I would like to show up in the new row that multiplies column B times column C. Example, if that that name is different than what you want it to be, keep all worksheets VeryHidden, or auto close it, or something else that renders the file useless.
I would like to allow users of this sheet to insert new rows into the sheet. Select the cell where you want the info to be pasted on 4. But the data in the protected sheet is not able to be selected and copied because of the user uncheck the Select locked cells and Select unlocked cells options when protecting the worksheet. One thing is for sure, Excel techies get very passionate about Excel :. The dragging issue must be another problem because it seemed to happen in all my workbooks, not only in the failing one. It's only when there's text or a formula in the cell that this is an issue. Now when I try to duplicate it, it doesn't work any more.