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:
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