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.

VBA Code – Copy protected workbook

The below VBA copy and whole protected workbook and save as a new workbook under the current workbook path.

Sub cpyWB()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Set newbook = Workbooks.Add
    defaultSheet = newbook.Sheets.Count
    For Each WS In ThisWorkbook.Sheets
        Set newWS = Sheets.Add(After:=Sheets(newbook.Sheets.Count))
        newWS.Name = WS.Name
    Next WS
    For i = 1 To defaultSheet
        newbook.Sheets("Sheet" & i).Delete
    Next i

    newbook.SaveAs ThisWorkbook.Path & "\Copy - " & ThisWorkbook.Name
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub



Wyman W
Compensation Survey Manager@Mercer
Wyman is human resources professional, 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

You may drop me a message if you are interested in HR consultancy services from Mercer.

Leave a Reply

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