Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved. Terms
of Use |
Performance issue when attempting to copy and paste a worksheet to a protected worksheet in Microsoft Office Excel 2007 and 2010.
When copying into a protected worksheet Excel will display the following informational message explaining that the worksheet is protected.
"The cell or chart you are trying to change is protected and therefore read-only. To modify a protected cell or chart, first remove protection using the Unprotect Sheet Command (Tools menu, Protection submenu). You may be prompted for a password."
"The cell or chart you are trying to change is protected and therefore read-only. To modify a protected cell or chart, first remove protection using the Unprotect Sheet Command (Review tab, Changes group). You may be prompted for a password."
In Excel 2007 and Excel 2010 the file will hang for a while and then eventually the message box will display after pasting entire sheet onto a protected worksheet. Excel needs to analyze each cell before message box displays.
The grid in Excel 2007 and Excel 2010 has increased from Excel 2003. If the user 'Selects All' in the older versions of Excel they will be selecting over 16 million cells, with the new increased Excel grid there are over 17 billion cells per sheet.
Anytime working in Excel performance will be better if you follow the best practice of selecting the range of cells that you want to copy.
To resolve this issue, follow these steps:
- On Sheet1, select the range of cells with the data you wish to copy.
- Copy the data.
- Click on Sheet2(Protected Sheet).
- Click Paste.
- You will see the following error display right away: "The cell or chart you are trying to update is locked and therefore read only."
To repro the issue where the message box doesn't appear right away
Select all of the cells on Sheet1(protected sheet).
- Copy the worksheet.
- Click on Sheet 2(protected sheet).
- Select Cell A1 and click the Paste button.
You will see a performance issue before it displays the error message: "The cell or chart you are trying to update is locked and therefore read only."
for other considerations.
Article ID: 2295758 - Last Review: July 28, 2010 - Revision: 1.0
- Microsoft Office Excel 2007
- Microsoft Excel 2010
- Microsoft Office Excel 2003
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate