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!
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!