Data Validation lost when Copy & Pasting

[ExcelNoob222]

Hi,

I have a drop down list in column D from rows 2-20. I have protected the sheet to prevent other things (like adding columns etc.). However, sometimes the data may go past row 20, so the user is instructed to copy and paste the last row to however many rows they need.

The problem is, the drop down data validation does not copy when the sheet is protected. If the sheet is not protected, it does copy. I have tried checking the "format cells", "format columns" and "format rows" options but none have worked.

The file must remain .xlsx so not sure if VBA can solve this? Thanks!!

 

[rlv01]

When you protect a worksheet you have to accept the tradeoffs. If you are willing to uncheck the 'locked' property for the cells in column D, that should allow your users to paste new cells without unlocking the whole sheet. .

 

[ExcelNoob222]

When you protect a worksheet you have to accept the tradeoffs. If you are willing to uncheck the 'locked' property for the cells in column D, that should allow your users to paste new cells without unlocking the whole sheet. .

Hi, yes I have done this and it allows them to copy and paste. The issue is the drop down list in not copying.

 

[rlv01]

Then I think VBA is your only other option.

 

[ExcelNoob222]

Then I think VBA is your only other option.

Do you know what the VBA would be? Im curious if it can be VBA in a seperate workbook that just gets ran on the sheet to lock it but doesnt actually live in the workbook. I might not have explained the original issue very well.

I have a protected sheet and on that sheet is a cell with a data validation drop down. The drop down works perfect with the sheet protected. However, when I copy that cell and paste it to another cell in the workbook (both cells are unlocked), the data validation drop down does not go with it. If the sheet is not protected, when I copy and paste the data validation drop down does go with it.

Thanks for your help!

 

[rlv01]

There are too many unknowns to reasonably answer your questions. I suggest you use the macro recorder to test out what you want to do. My own quick test seems to indicate it is possible to use VBA to add data validation to a cell on a protected sheet, if that cell is unlocked.