Re-Protecting Excel Sheet via Macro

Status
Not open for further replies.
Hello,

I have built a small, simple Macro in Excel which simply un-protects a sheet, copies and pastes some data from another sheet, and then re-protects the sheet. However, when I run the Macro, a prompt asking for a password to un-protect the sheet is asked, which is fine, then the copy and paste happens, however, I am not asked to enter a password to re-protect the sheet.

When I exit the Macro, the sheet appears to be protected, however, simply selecting the un-protect sheet option, will un-protect it without prompting for a password. Is there something I'm missing? I wish to re-protect the sheet, where a password is required.

Code:

Sub Macro2()

Sheets("Old").Select
ActiveSheet.Unprotect
Sheets("Totals").Select
Range("A9:S400").Select
Selection.Copy
Sheets("Old").Select
Range("A9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

Any help would be gratefully received, thanks.
 
Status
Not open for further replies.
Back