Error - You cannot use this command on a protected sheet - after macro runs successfully

Hello there,

I've setup a simple macro to copy the last sheet in a workbook, copy to the end, rename the sheet, update some formulas and clear some values back to zero. It works great, however, when I try to move to another sheet, after the macro has been run, I get the error above. I don't know if its because I'm just using a blanket protect command which protects everything? Do I need to be more specific? But, saying that, after several times dismissing the message, it doesnt return until the macro is run again.

Please Login or Register  to view this content.

See if this re-write of the code will work better.

Please Login or Register  to view this content.

Originally Posted by mjmitch

Thanks for the reply JLGWhiz, much appreciated, however the code got a runtime error 1004, 'Select method of range class failed'. Any ideas

Please Login or Register  to view this content.

You didn't really need that line anyhow. try this modified version.

Originally Posted by mjmitch

Update: the code is renaming the copied sheet, not the new sheet

Somehow, I got off on the wrong tangent from the original problem. The ActiveSheet.Protect command only sets the currently active sheet. So whatever is triggering the error message would be related to something in that active sheet. If you click the debug button when you get the message, it should show a highlighted line of code that can give you a clue to where the fault is. In the meantime, abandon the revised code that I suggested because it was targeting the wrong issue.