I'm creating a costing template for my company and have protected all columns that include a formula, leaving columns without a formula unprotected so users can hard key in data.
Previously, I just had the cells locked but when you copied and pasted the row, the new cells were unlocked and therefore the formulas where unprotected.
To solve this, I instead locked the whole column with formulas but now, the user is unable to copy and paste the rows at all. When I protect the sheet, I still allow access for the user the select locked objects as well as inset rows so I'm not sure where the block is:
Any tips or tricks please let me know! I've done a lot of googling to try and resolve but all the articles I've seen refer to vba that relies on all the cells being locked.
The closest thing I've found is the below VBA but it seems that this would only copy and paste the row into the next available spot whereas I need the user to be able to paste it in different parts of the document (ideally where they could select the row they'd like to paste after and the VBA would paste below that)
Sub Button1_Click() 'Change your password here Sheets("IGP").Unprotect "IGP"
Dim Rng As Long, i As Long Rng = Application.InputBox("Enter number of rows required.", Type:=1) For i = 1 To Rng 'Change source row and sheet name Range("a9").EntireRow.Copy Sheets("IGP").Range("a65536").End(xlUp).Offset(1).Insert Shift:=xlDown Next i
Application.CutCopyMode = False 'Change your password here Sheets("IGP").Protect "IGP"