Insert Row Include Formulas - Protected Sheet

[Kirrash]

Hello! I have created a workbook for supervisors to track employee case files. Formulas I use are often overwritten by unaware users. Frustrating as I always have to go back in and fix. My thinking then, is to lock the spreadsheet down and have a macro button to insert a new row (unlocking the sheet first so formulas will copy as well).

I have tried two separate ways, and both times column "I" just does not have the formula copied. Columns E, G, L, N, Q work as intended.

A look at the spreadsheet (cursor currently on I6 where formula has not been copied.

"Button 41" at top left is what initiates the macro.

I have tried two different codes to achieve this - and both do not copy column I. Any ideas?

Thank you!

p.s. this is my first posting so if I need to seriously rework this tell me and I will jump on it.

File _Sample Macro Fill2.xlsm code:

Sub Button41_Click()
 
Dim PswS As String
pswStr = "123"

On Error Resume Next

Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:=pswStr

ActiveSheet.ListObjects("Table1").ListRows.Add

ActiveSheet.Protect Password:=pswStr
Application.ScreenUpdating = True
End Sub

============================
_Sample Macro Fill3.xlsm:

Sub Button41_Click()
Dim pswStr As String
pswStr = "123"
On Error Resume Next
Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:=pswStr
ActiveSheet.Range("D1").Select
'D8 is table header
Range("Table1[[#Headers],[e]]").Select
Range("Table1[[#Headers],[g]]").Select
Range("Table1[[#Headers],[I]]").Select
Range("Table1[[#Headers],[L]]").Select
Range("Table1[[#Headers],[N]]").Select
Range("Table1[[#Headers],[Q]]").Select


Selection.End(xlDown).Select
Selection.ListObject.ListRows.Add AlwaysInsert:=False
ActiveSheet.Protect Password:=pswStr
Application.ScreenUpdating = True
End Sub

 

[gplhl]

This will be more than likely due to you having created the formula or modifying it after you created the table. If formulas are all the same in a column, change back to a range and then re-format as a table, new lines added at the bottom of the table will then auto copy the formula down.

 

[Kirrash]

This will be more than likely due to you having created the formula or modifying it after you created the table. If formulas are all the same in a column, change back to a range and then re-format as a table, new lines added at the bottom of the table will then auto copy the formula down.

Good morning! That was an excellent idea. Converted to range, reformatted as table - same result. Macro inserts row and formulas on all columns but I. I'll keep trying. Appreciate the input.

 

[Kirrash]

Good morning! That was an excellent idea. Converted to range, reformatted as table - same result. Macro inserts row and formulas on all columns but I. I'll keep trying. Appreciate the input.

Actually, it WORKED! I tried using the 2nd excel _Sampe Macro Fill 3. Amazing! My life just became SO much simpler. Thank you for your guidance.