How to copy data from protected sheet?
Supposing, you receive an Excel file from another person which with worksheets are protected by the user, now, you want to copy and paste the raw data to another new workbook. 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. This article, I will introduce some quick tricks for solving this task in Excel.
Excel Productivity Tools
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately. 60-day Unlimited Free Trial
The following simple code can help you to copy data in active protected sheet, please do as this:
1. Activate the protected sheet which you want to copy the data from, and then hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window, then press Ctrl + G Keys to open the Immediate window, and then copy the below script code into the Immediate window:
2. And press Enter key, then you should press Ctrl + C to copy the sheet data, and then activate other workbook or a new worksheet where you wish to paste this sheet, and place the cursor in cell A1, and then hold Ctrl + V to paste it.
If you need to copy a specific range of data from the protect sheet, you can apply the Name Box to finish this job.
1. Input the cell range (D2:H13) into the Name Box directly, and press Enter key, see screenshot:
2. After pressing Enter key, nothing happens apparently. Please don’t do anything, just press Ctrl + C to copy the specific range, then you can see the data in the range D2:H13 has been surrounded by the dotted line, see screenshot:
3. Then press Ctrl + V to paste it to another worksheet where you want to copy the data.
Excel Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 3 days agoThanks - just what I needed
To post as a guest, your comment is unpublished.· 2 months agoGreat help ... thanks
To post as a guest, your comment is unpublished.· 5 months agothanks a lot for the tricks, this helped me a lot :)
To post as a guest, your comment is unpublished.· 7 months agoThanks a lot dear, your trick are good. it made my work simpler
To post as a guest, your comment is unpublished.· 8 months agoamazing trick