copy formula from previous row to next row with protected sheet enabled

[EdStockton]

I have a worksheet with 8 columns for calculating depreciation. The first four columns are user input data including an account number, starting date, cost, and years of asset life. The next four columns would calculate deprecation per year, accumulated depreciation as of the prior fiscal year, current year depreciation, and accumulated depreciation as of the end of the current fiscal year. I would like to protect the formulas in the last four columns. However, I would like to be able to copy those formulas to the subsequent row once a user provides the necessary data in the first four columns. Is that possible?

 

[hotabae]

I recommend looking into the 'protect' property - UserInterfaceOnly. It allows macros/code to make changes, while the user cannot.

Shown (in part) Here:

.Protect Password:=”password”, UserInterFaceOnly:=True

Here is a link with a little more info, but I recommend you do a little searching yourself.

https://www.mrexcel.com/forum/excel...heet-while-allowing-vba-freely-unprotect.html

Edit: Upon reading the next response... maybe I misunderstood the situation?

 

[Fluff]

Excel should do that for you.
if you have the formulae in (i think) 3 of the previous 5 rows, then it should get copied down automatically.

 

[EdStockton]

hotabae and Fluff,

Thank you for your replies.

hotabae I looked at the link you supplied but it seemed to be missing something. I'm not sure what.

Fluff, I have 5,522 rows. Columns A through J are to be completed by the user. However, only columns F, G, H, and J are used in the calculation columns which are columns K through N. It may be because many of the columns are not necessary, but the formulas are not automatically copied to the subsequent or next row. Am I doing something wrong here.

I did create the following macro which works OK, but the user has to know how to use a macro, which is a problem even with the shortcut keys.

Sub test()
ActiveSheet.Unprotect "password"
With ActiveCell
.EntireRow.Copy
.Insert Shift:=xlDown
End With
Application.CutCopyMode = False
ActiveSheet.Protect "password"
End Sub

This does copy the formulas but it also copies all the other data in the previous row. I am not real good at coding. Only know a few things.

If you can improve on what I have, I would appreciate it.