Macro that will unlock sheet, perform insert row with formulas copied down, then relock

[UTdawg]

Hi all,

I'm hoping someone can help me with this. I have a protected excel sheet with 3 columns (all contain formulas) that I do not want others to be able to edit. These 3 columns are locked. What I need is a macro that will unlock the sheet, perform the insert row function whilst copying down the formulas from the row above, and then protect the sheet. Below is the most recent macro that I've been trying to use:

Sub InsertRowFormulas()
Sheet1.Unprotect Password:="TParks19"
Selection.EntireRow.Insert
For Each cell In Intersect(ActiveSheet.UsedRange, Selection.Offset (-1, 0). EntireRow)
If cell.HasFormula Then
cell.Copy cell.Offset (1, 0)
Sheet1.Protect Password:="TParks19"
End If
Next
End Sub

This macro does what it is supposed to for column M (formula comes down with inserted row), but not O or P.

I'm at a loss here. Any help would be greatly appreciated.

 

[Fluff]

Removed, misread OP.

 

[UTdawg]

Removed, misread OP.

Unfortunately this didn't quite work. It inserted the row, but didn't bring any of the formulas down, including in column M which it had previously done. It also didn't reprotect the sheet.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub InsertRowFormulas()
   Sheet1.Unprotect Password:="TParks19"
   With Selection.EntireRow
      .Insert
      Intersect(.Offset(-2).Resize(2), Range("M:M")).FillDown
      Intersect(.Offset(-2).Resize(2), Range("O:P")).FillDown
   End With
   Sheet1.Protect Password:="TParks19"
End Sub

 

[UTdawg]

Hi & welcome to MrExcel.
How about

Sub InsertRowFormulas()
   Sheet1.Unprotect Password:="TParks19"
   With Selection.EntireRow
      .Insert
      Intersect(.Offset(-2).Resize(2), Range("M:M")).FillDown
      Intersect(.Offset(-2).Resize(2), Range("O:P")).FillDown
   End With
   Sheet1.Protect Password:="TParks19"
End Sub

Thanks Fluff. I figured out what was wrong. I had to move the "protect" line down to just before End Sub. I was apparently reprotecting too early. Hence why column M worked like it should have but not O or P. I do really appreciate your help though. Definitely got me to take a step back and lose my tunnel vision.

 

[Fluff]

Glad you got it working & thanks for the feedback

 

[Mischief63]

Hi I'm trying to do this with a TABLE A that has Columns A - AG. Column A is a dropdown and AG requires user entry. The remaining columns are all formulas.

I wanted a button for them to click that:

unprotects the table,
adds the row and fills down the formulas,
then user enters the data
then re-protects.

No luck so far. Any thoughts?