Protect formulas but allow adding and hiding rows

[estimating newb]

new to VBA and not sure how to make this work.
I have a spreadsheet and want to protect it bc it has formulas in some columns that I don't want to get accidentally deleted. But I need to be able to add or hide rows, while copying the formulas from rows above.
I tried to create a macro for this but it just adds a row at whatever row I click on when making the macro.

any help would be super appreciated.

Thanks,
New to this

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #1 requires good titles. "dumb newb question/help" is not a good title. I have updated it for you this time because you are a new member. --6StringJazzer

[6StringJazzer]

Welcome to the Forum estimating newb!

1. CTRL+a to select all cells
2. Format, unselect Lock cell, to unlock all cells
3. Select the cells with formulas that you want to protect
4. Format, select Lock cell, to lock selected cells
5. Select Review, then on the Protect panel select Protect Sheet
6. Check Select locked cells, Select unlocked cells, Format Rows, Insert Rows.

Now your formulas cannot be changed but you can copy the formulas to other cells, and insert and hide rows.

I'm not sure why you are expecting to use VBA for this. If this solution does not work then please attach a file with sample data (see yellow banner at the top of the page)

[estimating newb]

I've tried this and when I insert a row, the formulas are not copied into the new row. That's why I thought I needed code for this

[estimating newb]

I have included a sample of the workbook. it's actually hopefully going to be a template, but we shall see. anyways, when I try to follow the instructions you provided, the formulas in the various columns do not automatically copy. Part of the purpose of this template is for the formulas to come thru automatically.

[kvsrinivasamurthy]

worksheet Event Code

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

Dim LR As Long, Ro As Long, TRos As Long
Dim Cel As Range
LR = Range("A" & Rows.Count).End(xlUp).Row
If Not Intersect(Range(Target.Address), Range("$1:$" & LR)) Is Nothing Then
Ro = Target.Row
TRos = Target.Rows.Count
For Each Cel In Range("C" & Ro - 1 & ":M" & Ro - 1)
If Cel.HasFormula Then Cel.Copy Range(Cel.Offset(1, 0), Cel.Offset(TRos, 0))
Next Cel
End If

Application.EnableEvents = True
End Sub

Worksheetevent

To paste the code
Right click on Sheet tab --> view code
Visual Basic (VB) window opens.
Paste the code
Close the VB window.

[estimating newb]

ok. what am i doing wrong? I can see where you have added a few rows in testing and looks like the formulas copied.
how are you inserting the rows? I tried highlighting the whole row and also the insert row on the ribbon and both of them are not copying in the formulas.

[6StringJazzer]

ok. what am i doing wrong?

You do not need to Reply With Quote every time you reply to a post. Just Reply unless there is something specific you really need to quote.

[kvsrinivasamurthy]

Pl up load file in which you have problem.
If code is copied correctly.
Select rows say 5 to 7. Right click. --> Insert.
Code insert rows and copies all formulas.
Note: you have merged column A and B. Merging should be avoided to the maximum extent possible. Here there is no necessity of merging. Pl avoid merging.
Merging creates lot of problem to run a code or applying formulas.

[estimating newb]

Ok. Well, I'll just leave then as it seems I am not responding or asking the questions you want the way you want. So sorry to have wasted your time.

[6StringJazzer]

Ok. Well, I'll just leave then as it seems I am not responding or asking the questions you want the way you want. So sorry to have wasted your time.

Not a waste of time at all, and it seems that kvsrinivasamurthy is on track to get you the solution you need. We just request that people spend a couple of minutes reading our rules when they come on board, which makes things run smoother for everyone.

[estimating newb]

Oh yes, it seems he/she was on the right track. but with the attitude and critiques for simply asking questions being thrown at me, by a moderator of all people, I think I'll be better served looking elsewhere for help. now and in the future.