Delete/Copy Paste around Locked Cells

ExcelNoob222

New Member
Joined
Jun 17, 2020
Messages
34
Office Version
  1. 365
Platform
  1. Windows
Hi all,

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

ItemCostQtyTotal Cost
A$1.002$2.00
B$2.004$8.00
C$3.006$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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
What do you want the behaviour to be?
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

ExcelNoob222

New Member
Joined
Jun 17, 2020
Messages
34
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 17, 2020
Messages
34
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
glad you have a solution that you are happy with
 

ExcelNoob222

New Member
Joined
Jun 17, 2020
Messages
34
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,999
Messages
5,622,115
Members
415,878
Latest member
jjj12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top