Allow paste in worksheet without overwriting locked cells
- by jjeaton
I have a protected worksheet that users would like to copy and paste into. I have no control over the workbook they are copying from.
The protected worksheet has some rows that are available for data entry, and other rows that are locked and greyed out to the user. The users would like to be able to paste over the top of the entire worksheet from another random workbook and have all the cells available for data entry filled in, while the locked cells are undisturbed. In the current state, the user gets an error when they try to paste, because it cannot paste over the locked cells.
Example:
Worksheet 1:
Act1 100 100 100
Act2 100 100 100
Act3 100 100 100
Worksheet 2: (The second row is locked)
Act1 300 300 300
Act2 200 200 200
Act3 100 100 100
After copying/pasting Worksheet 2 should look like this:
Act1 100 100 100
Act2 200 200 200
Act3 100 100 100
The values from worksheet 1 are populated and the locked rows are undisturbed.
I've been thinking along the lines of having a hook where on paste, the locked cells are unlocked so that the paste can happen, and then are reverted to their original values and relocked.
Is there some way I can loop through the cells in the clipboard and only paste cells where the target isn't locked?
It is preferable to not create a separate button for paste, so there is less impact on the users, but if that's the only way, I'm not opposed to it.
Currently, I plan on grouping the locked rows together, so that the data entry cells are contiguous, but then the accounts will be out of order, which is not preferred.
