How to copy data from a range where you cannot select, due to worksheet protection?
Here’s the situation: You receive a worksheet with data but not insight. Well, it happens and you get use to it already. You plan to copy the (raw) data and paste it to a new worksheet and do some analyses on your own. 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. (Of course you do not know the password to unprotect the worksheet.)
What are you supposed to do then? Print a hard copy and then re-input the data one by one? If this is the approach you used, you’d better read this.
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.
Do the following:
IMPORTANT: Once you hit Enter, nothing happens apparently. Press Ctrl+C (before you hit or click on anything else).
Now you should see the “marching ants” surrounding A4:E8. It means that you have put that range into clipboard successfully for Pasting to somewhere else.
The next simple action is to go to a new worksheet or a blank worksheet and do the normal Paste (Ctrl+V).
There you go! Without the hassles of re-input manually. 🙂
Thoughts of protecting cells from selecting
Many people do that without a particular reason. “Just do it” is their motto in workplace, maybe. Nevertheless, there is one good reason I would protect locked cell from selecting. It is to make navigation easier for user’s input. With locked cells protected from selecting, while allowing “unlocked” cells to be selected, user could then focus on where they need to input. Although it is not perfect, it reduces chance of inputting data to irrelevant locations. 🙂
If human errors can be avoided totally, it is not human errors at all.
wmfexcel 
Appreciate the info, but when I have the section of the workbook selected that I want to copy, I press Ctrl C, I do not get the “marching ants”, and go to a blank workbook and paste and I only get two little boxes with question marks in them. When I select in that field where I just pasted it is empty except for a space? Any guidance here?
LikeLike
Hi Matt. That’s strange.
My guess is the Copy action did not properly take place. You may try again by showing the clipboard to confirm you have “copied” what you need successfully.
Hope It helps.
LikeLike
Awesome, this saved me ours of time. TC
LikeLike
Glad it helped! 😀
LikeLike
Works really well! Thanks!
LikeLike
Glad it helped. 😀
LikeLike
Hi, can you help me please. I cannot copy the whole worksheet even if I follow your instructions.
Please help
LikeLike
When I try this method and record it into a macro, the namebox selection is not recorded. How do I register the cells which are input into the namebox into the macro?
LikeLike
Hi Simon,
Try
Range(“A1:C10”).Copy
LikeLike
Great. Thanks for your help — that worked, and I should have been able to figure that one out…!
LikeLike
Welcome 😁
LikeLike
THANK YOU SO MUCH! I would have never figured that out
LikeLike
Glad it helps 😃
LikeLike
How do you do this with, sheets that have formulas in it. Cause the sheets I have the formulas will not be moved with it.
LikeLike
Hi Brian,
Could you please elaborate your question a bit?
LikeLike
Sorry, I was meaning if its like keeping track of sports data, like points plus/minus and automatic. http://www.weglobalfootball.com/downloads/ here is the link to the data sheets I was wanting to work on, and change around, for my own stats and fun. Someone on Answers went off me and saying it was wrong of me trying to change stuff. I was trying to just get blank sheets so I can play a tournament and keep track for it on X-box.
LikeLike
THIS TRICK NOT APPLY IN STORNGLY PROTECTED SHEET
LikeLike
Hi Gyan,
Can you elaborate more on “strongly protected sheet”?
LikeLike
Really helpful!!! thanks bro!!
LikeLike
Welcome 😀
LikeLike
This is BRILLIANT and you are AMAZING!!!
THANKS A TON! 🙂
LikeLike
Thanks! Glad it helps 😁
LikeLike
Thanks Dude this helped me a lot 🙂
LikeLike
Welcome 😄
LikeLike
You just saved my TONS of time. Thank you! 🙂
LikeLike
Welcome 😁
LikeLike
Just used in 2017, saved me loads of time. Thanks
LikeLike
You are welcome 😀
LikeLike
The range idea allowed me to select the section of the workbook that was hidden but the data wasn’t automatically copied. However, I was able to copy and paste then! Thank you!
LikeLike
Done, thanks a lot, this made the entire exercise easy.
LikeLike
You are welcome! Glad it helps
LikeLike
Thanks. Made my working day 🙂
LikeLike
You are welcome!
LikeLike
THANKS ITS FANTASTIC
LikeLike
Welcome.
Glad it helps.
LikeLike
Fantastic! super hack
LikeLike
Glad it helps. 🙂
LikeLike
THANK YOU VERY MUCH…… 🙂
LikeLike
Very welcome 😀
LikeLike
Thanks guys.. really helped.
LikeLike
Glad it helps! 🙂
LikeLike
Awesomeeee! Used in 2016
LikeLike
Glad you like it !
LikeLike
You are welcome ! 😀
LikeLike
you saved the day!!! this saves my company so much wasted time and energy. thank you.
LikeLike
You are welcome!
Glad it helps 😀
LikeLike
Thank you from another happy user!!!
LikeLike
Wow! Now you might be able to solve my big question here:
I can copy-paste from a protected sheet here. However, it contains hiddent rows and when I copy-paste it, the hidden rows are included. Since it is protected, I cannot copy only visible row as I cannot access F5… Is there a different way to do so? I have a huge worksheet with severals sheets… and a mega headache!
Thanks in advance 🙂
LikeLike
On top of my mind, you may try
1) Alt+; this is the shortcut for selecting visible cell. Try this before you copy
2) if the above doesn’t work, you may press Ctrl and then W right after you paste the data. This action will open the paste options where W means keep source column width, which should make the hidden columns hidden again.
I haven’t tried the above as i am not with my PC.
Hope either one helps 😁
LikeLike
Thank you thank you thank you!!!
LikeLike
Welcome welcome welcome! 😁
LikeLike
I like this one!
LikeLike
Nice!
LikeLike
Cool!! This works even if cells/rows/columns are hidden as long as the range encompasses them.
Total hack!! I love it.
LikeLike
Exactly!
Cheers,
LikeLike