Unprotect/re-protect sheet in macro

[bertilak]

Can this be done: unprotect then re-protect a sheet from within a macro?

The reason I ask is that I have already been told that a table will not automatically extend to include a row added to the end unless the sheet is unprotected. I have been putting up with this but have recently added a button with a macro to copy and paste that new row to the bottom of the table, instead of doing the copy/paste manually.. Here is the existing code:

Range("F8:J8").Copy
Cells(Rows.Count, "F").End(xlUp).Offset(1, 0).Select
ActiveCell.PasteSpecial Paste:=xlPasteValues

Is there something I can add above this code to unprotect the sheet then something after it to re-protect?

Bonus points for being able to save and restore the initial protection status!

 

[Vogateer]

This should do it.

Sheets("Sheet1").Unprotect
[rest of your code]
Sheets("Sheet1").Protect

 

[bertilak]

This should do it.

Sheets("Sheet1").Unprotect
[rest of your code]
Sheets("Sheet1").Protect

Hi.

I get run-time error '9' :
Subscript out of range.

"Sheet1" is the correct name of the sheet.

 

[Vogateer]

That's usually the error you get when Sheet1 doesn't exist. Try this and see if it works:

    Sheets(1).Protect
    Sheets(1).Unprotect

 

[bertilak]

That's usually the error you get when Sheet1 doesn't exist. Try this and see if it works:

    Sheets(1).Protect
    Sheets(1).Unprotect

That works. Thanks!

(The Unprotect needs to come first!)