Hi there everyone,
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"
End Sub
Thanks so much in advance for any help!