Copy data from strictly-protected sheet

How to copy data from a range where you cannot select, due to worksheet protection?

Excel Tips - Copy protected cellExcel Tips - Copy from stickly-protected sheet

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:

Excel Tips - Copy from stickly-protected sheet 2

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).

Excel Tips - Copy from stickly-protected sheet 3

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.

Advertisements
This entry was posted in Excel Tips and tagged , . Bookmark the permalink.

48 Responses to Copy data from strictly-protected sheet

  1. D Simon says:

    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?

    Like

  2. Jeanne M Knight says:

    THANK YOU SO MUCH! I would have never figured that out

    Like

  3. Brian Johnson says:

    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.

    Like

    • MF says:

      Hi Brian,
      Could you please elaborate your question a bit?

      Like

      • Brian Johnson says:

        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.

        Like

  4. GYAN says:

    THIS TRICK NOT APPLY IN STORNGLY PROTECTED SHEET

    Like

  5. Halefom T says:

    Really helpful!!! thanks bro!!

    Like

  6. Sudz says:

    This is BRILLIANT and you are AMAZING!!!
    THANKS A TON! 🙂

    Like

  7. Thanks Dude this helped me a lot 🙂

    Like

  8. Kristal says:

    You just saved my TONS of time. Thank you! 🙂

    Like

  9. Nigel says:

    Just used in 2017, saved me loads of time. Thanks

    Like

  10. Sam says:

    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!

    Like

  11. Rizwan Shah says:

    Done, thanks a lot, this made the entire exercise easy.

    Like

  12. Sandra says:

    Thanks. Made my working day 🙂

    Like

  13. ALEX says:

    THANKS ITS FANTASTIC

    Like

  14. SK says:

    Fantastic! super hack

    Like

  15. RATISH RAVI says:

    THANK YOU VERY MUCH…… 🙂

    Like

  16. Nanda Kumar says:

    Thanks guys.. really helped.

    Like

  17. S D says:

    Awesomeeee! Used in 2016

    Like

  18. MF says:

    You are welcome ! 😀

    Like

  19. Claire in NZ says:

    Thank you from another happy user!!!

    Like

  20. Marie-Claude Hardy says:

    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 🙂

    Like

    • MF says:

      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 😁

      Like

  21. Michelle says:

    Thank you thank you thank you!!!

    Like

  22. Igor says:

    I like this one!

    Like

  23. Dr. Demento says:

    Cool!! This works even if cells/rows/columns are hidden as long as the range encompasses them.

    Total hack!! I love it.

    Like

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s