TechSpot

Re-Protecting Excel Sheet via Macro

By timmy1982
Jan 22, 2008
  1. 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.
     
Topic Status:
Not open for further replies.

Similar Topics

Add New Comment

You need to be a member to leave a comment. Join thousands of tech enthusiasts and participate.
TechSpot Account You may also...