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
  ActiveSheet.Paste
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
        ThisWorkbook.Sheets(WS.Name).Cells.Copy
        newbook.Sheets(WS.Name).Paste
    Next WS
    
    For i = 1 To defaultSheet
        newbook.Sheets("Sheet" & i).Delete
    Next i

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