Excel VBA copy contents of protected worksheet

This Excel VBA tutorial explains how to copy contents of protected worksheet and then paste contents in another worksheet.

You may also want to read:

Excel VBA protect worksheet with password

Excel VBA hide worksheet with password (xlVeryHidden)

Copy contents of protected worksheet

Some authors would protect worksheets with password so that other people cannot modify and not even able to select the cells. I usually do it when I setup templates for users to fill in the data but I don’t want them to mess up my formula, so I protect my formula cells and allow them to modify other cells.

There is absolutely no way to modify the protected worksheet without knowing the password, and you cannot manually copy the contents because you are not allowed select the cells.

However, with the help of VBA, you can easily copy the contents and paste to another worksheet without clicking on the cells.

VBA Code – Copy contents of protected worksheet

Suppose you want to copy all contents and formats from a worksheet called “protected ws”, and then paste to a worksheet called “new ws”, then use the below VBA code.

Public Sub copyContents()
  Sheets("protected ws").Cells.Copy
  Sheets("new ws").Range("A1").Select
End Sub

This VBA is very simple to use, just change the worksheet names. This VBA not only works for protected worksheet, but also work for normal worksheet if you want to copy contents.


Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *