Delete/Copy Paste around Locked Cells

[ExcelNoob222]

Hi all,

Suppose I have the below data in columns A to D:

Item	Cost	Qty	Total Cost
A	$1.00	2	$2.00
B	$2.00	4	$8.00
C	$3.00	6	$18.00

Column D is a formula and is locked to prevent editing. Column A to C are unlocked to allow for data entry. I want users to be able to copy and paste a row to prevent having to manual key identical data. However because column D is locked I cannot do this. Is there a way to have column D locked, select the entire row (say row 2) copy everything but the locked cell and then paste it into say row 4.

I also want them to have the ability to select the row and press delete, deleting all of the data entry cells in the row but not the locked cell.

My real data is approximately 30 columns with data entry cells scattered. They are not all in a row so selecting columns A to C of a row will not work. Any ideas?

 

[Yongle]

What do you want the behaviour to be?

 

[ExcelNoob222]

What do you want the behaviour to be?

I only want it to work on a specific sheet. I do not want them to move over to another sheet and have them use the shortcut and then be able to copy paste on that sheet.

 

[ExcelNoob222]

What do you want the behaviour to be?

Figured out a solution. I made the PW on the sheet I want the macro to work on different then the other sheet passwords. If they use the keyboard shortcut on another sheet, they will still get the copy row? prompt and if they hit okay and then select a paste row, nothing will happen.

 

[Yongle]

glad you have a solution that you are happy with

 

[ExcelNoob222]

glad you have a solution that you are happy with

Thank you for your help! This made a huge difference.

 

[ExcelNoob222]

Hi,

I have another question regarding this, not sure if I should start a new thread but will try to post on here to start.

I am looking for and adjustment to the code. Currently it works by selecting a cell and then running the macro and then selecting the number of rows you want to paste the selected cell row to. For example if I select A2 and run the macro, then select cels A3-A5, it will copy all of row 2 to rows 3-5.

I am hoping to tweak to now work where if you select cells A2 to A5 and run the macro, it will copy rows 2 to 5 and you select a destination cell and it will paste rows 2 to 5 starting in the cell picked. So if I selected A2 to A5 and ran the macro and then selected cell A6. It would copy rows 2-5 and then paste them in row 6-9.

Hopefully that made sense.