Macro to unprotect then reprotect sheet only works once

[Puffy1]

This macro is supposed to start by unlocking the protected sheet named "RANKER", then format and organize my ranker, and end by re-protecting that same sheet. As you might have guessed by the fact that I'm writing this, there is a problem. The macro works fine except for one thing: When I paste in my next set of info after the macro runs successfully (the next time I want to run a ranker), When I go to past my data into Q6:Q14, I get an error stating that the sheet is password protected; even though I have what I assumed was a working protect/unprotect system at the beginning and end of my macro. To clarify, when I initially open this spreadsheet, paste in my info, and run the macro, it runs perfectly by unlocking the sheet then relocking it. However when I attempt to run it again, it's almost as if my unprotect line is no longer effective. Think of it this way, it's as if my unprotect/protect lines are only good for 1 single use, and then stop working. Any help would be greatly appreciated.

Please Login or Register  to view this content.

Moderators Note: Please follow Forum Rule #3 and use code tags. Added this time, but please use them in the future…Thanks.

[AlphaFrog]

Some of your references are ActiveSheet and ActiveCell. If the ActiveSheet is protected and it is not Ranker, you will get an error.

[AlphaFrog]

Try this (not tested).

Please Login or Register  to view this content.

[Puffy1]

Hmmm...getting a compile error toward the end.

[Puffy1]

Thanks. Yeah, that's the strange part. ActiveSheet is in fact Ranker. Should I just change from ActiveSheet and just name the sheet?

[Winon]

Also see if you don't perhaps have Sheets("Ranker") protection in any other Code of your Project, for instance, Sheet Activate() etc.