Macro NOT copying formulas

[FranktheBank]

Hi,

I have a spreadsheet I'm using as a form for managers to complete (see attached) it's password protected so that the user can only access columns: C, D, E, F, J, K, N, O, R & S.

I have a 'floating' Command button which activates a macro to remove password, insert a blank line and reinstate password. The problem is the new line is not copying the formulas nor the Vlookup in columns G,H,I, L,M, P,Q,T & U.

I tried

Private Sub New_Line_Insert_Click()
    ActiveSheet.Unprotect Password:="Assumption"
    ActiveCell.Offset(1).EntireRow.Insert shift:=xlDown, copyorigin:=xlCellTypeFormulas
    ActiveSheet.Protect Password:="Assumption"
End Sub

AND

Private Sub New_Line_Insert_Click()
    ActiveSheet.Unprotect Password:="Assumption"  
    ActiveCell.Offset(1).EntireRow.Insert shift:=xlDown, copyorigin:=xlFormatandformulaFromAbove
    ActiveSheet.Protect Password:="Assumption"
End Sub

Neither worked.

Any and all insight would be appreciated.

 

[Luke M]

Perhaps something like this? Copies, pastes and inserts, then clears non-formulas.

Private Sub New_Line_Insert_Click()
    Dim r As Range
    Const myPass As String = "Assumption"
    Set r = ActiveCell
   
    Application.ScreenUpdating = False
   
    With ActiveSheet
        .Unprotect myPass
        r.EntireRow.Copy
        r.Offset(1).EntireRow.Insert shift:=xlDown
        'Clear constants
        r.Offset(1).EntireRow.SpecialCells(xlCellTypeConstants).ClearContents
        .Protect myPass
    End With
   
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

 

[FranktheBank]

Hey worked Great!!