macro to unprotect sheets

  • I am trying to make changes to several excel sheets at the same time. I have a macro that will unprotect a sheet and make all the changes required and then print out the results. Is there a way to unprotect a set of sheets that are grouped together? I need to change about 2400 sheets real quick!

  • if they all have the same password...

    Sub unprotectmulti()
    Dim ws As Worksheet
    For Each ws In Worksheets
        ActiveSheet.Unprotect Password:="password"
    Next ws
    End Sub

    Hope this helps

  • Hey, I can't believe how quickly you answered this! I put this into my macro with the following results. (1) It worked great-on (2) sheets. What do I need to add to get it to unprotect all the sheets? (2) How can I get this to run together with the other macro?:)

  • Hi Matrix -

    The code Will posted will unprotect ALL worksheets in the active workbook (if the unprotect password is the same on all sheets). If you are working on multiple workbooks at the same time, you would need to run the loop for each workbook.

    To get it to run with your other macro, you can either put the 4 lines of code as the beginning of your macro (the Dim line - through the Next ws line) - or - you can Call it from your main macro

    Put the line - - -
    Call unprotectmulti

    in your macro where you need the sheets to unprotect.

    Hope this helps.


  • Hi,

    Actually there is a slight typo in Will's code. The unprotect line should read:

    ws.Unprotect Password:="password"

    ie it should reference the 'ws' worksheet variable and not the Activesheet, which doesn't change in the code.


  • :biggrin: Cheers Ed! I'll have to stay off the beer.....

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!