I have a workbook with multiple worksheets that are protected, I need to copy and paste the values from a named worksheet into a new and separate workbook. Lets say one worksheet is called ‘Property’
What I have found so far copies the source so needed the protection removing, I can do this but cannot then automate the protection being re-applied to the original sheet instead it protects the new workbook.
Sub CopyDisposals()
' Declare variables
Dim sourceWs As Worksheet
Dim newWb As Workbook
' Set the source worksheet you want to copy from the active workbook
Set sourceWs = ThisWorkbook.Sheets("Disposals")
ActiveSheet.Unprotect "*****"
' Copy the entire source worksheet to a new workbook
sourceWs.Copy
Set newWb = ActiveWorkbook
' Activate the copied worksheet in the new workbook
newWb.Sheets(1).Activate
' Copy the used range of the active sheet to clipboard
ActiveSheet.UsedRange.Copy
' Paste the copied data as values into the active sheet starting at cell "A1"
ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
' Clear clipboard to remove the dashed line around the copied range
Application.CutCopyMode = False
ActiveSheet.Protect "*****"
End Sub
New contributor
user27367068 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
Sub CopyDisposals()
' Declare variables
Dim sourceWs As Worksheet
Dim newWs As Worksheet
Dim newWb As Workbook
' Set the source worksheet you want to copy from the active workbook
Set sourceWs = ThisWorkbook.Sheets("Disposals")
sourceWs.Unprotect "*****"
' Create a new workbook
Set newWb = Workbooks.add
Set newWS = newWb.Sheets(1)
' Copy the used range of the source worksheet to the new worksheet
newWS.Range(sourceWs.UsedRange.Address).value = sourceWs.UsedRange.value
sourceWs.Protect "*****"
End Sub
(Note, I have not tested this, but it should give you an idea of where to go
0