Forum Discussion
Solved
Prevent a single protected cell from being copied over in the ROW?
I am trying to Copy/Paste_Values in a ROW where only one cell is protected (and that cell needs to retain its formula).
I get an error that a "cell is protected..." and none of the other cells in the ROW have been updated. They still contain their original content.
Is there an easy way around this?
- I don't think so. to copy to a new range all the cells in that new range must be unlocked. you could come up with some macro solution to either create a function that copies cell by cell and then ignore the protected cell(s) or something.
I suggest just re-arrange the sheet accordingly or create a data range and a constant/locked range and then create an 'output' range that "copies" the data into the format you want. For example A, B, D, E should copy but C is fixed then in M, N, O, P you have the "copy" ranges and in Q is fixed and formulas in A:E are =M, =N, =Q, =O, =P (notice how I inserted =Q in the middle)
so then you can copy down in columns M:P and col Q is locked but in columns A:E it shows the 'copy' values on either side of the fixed C value.
- I don't think so. to copy to a new range all the cells in that new range must be unlocked. you could come up with some macro solution to either create a function that copies cell by cell and then ignore the protected cell(s) or something.
I suggest just re-arrange the sheet accordingly or create a data range and a constant/locked range and then create an 'output' range that "copies" the data into the format you want. For example A, B, D, E should copy but C is fixed then in M, N, O, P you have the "copy" ranges and in Q is fixed and formulas in A:E are =M, =N, =Q, =O, =P (notice how I inserted =Q in the middle)
so then you can copy down in columns M:P and col Q is locked but in columns A:E it shows the 'copy' values on either side of the fixed C value.- Thanks for the detailed reply. Your solution was the same as what I have been doing, but manually. My spreadsheet has evolved over several years and contains 68 columns together with many related sheets. But the real snag is I would have to update all the formulas where I used INDIRECT, which are many. So changing the column order is not really an option for now.
I was even hoping someone had a Power Tool addon they could recommend (I have Kutools but it doesn't do what I need).
Anyway, thanks again.- my recommendation is to NOT use INDIRECT unless absolutely necessary. If you share a sample of the sheet and what you are trying to do, in nearly all cases we can find a way to do it without INDIRECT or at least minimize its use (e.g. only to a few defined 'names'
