I want to copy a given range in Excel, which has rows hidden due to filtering out, into another range which has other rows filtered out.
I applied the usual procedure Ctrl+C, Ctrl+V. It is as if it copied only the visible range (undesired), and pasted as value (undesired). I expected this to work as if no filters were applied.
Now, the detail.
I have an Excel workbook with 115 worksheets, say, Sheet001 to Sheet115. All of them contain the same data (covering columns A to AA, and rows 5 to 169), by virtue of using formula
in cell A6, for all sheets from Sheet002 to Sheet115. Similar formulas appear across the whole range above specified. Row 5 is reserved for headings, and it is explicitly copied instead of assigned with formulas. It is used for filtering.
The difference among sheets is in the filters applied to various columns. E.g., Sheet002 (to be used as source) only shows row 29. Sheet003 (to be used as target) only shows row 28.
Now, I insert column Z in all Sheets.
I enter in Sheet002 formulas
Z28 : =Sheet001!Z28 Z29 : =Sheet001!Z29
All other cells in range Sheet002!Z6:Z169 are empty. I mean to copy this into Sheet003.
If I select range Sheet002!Z5:Z170 (heading + data + 1 more row), copy, and paste it into Sheet003!Z5, I expected to get the following:
Z5 : the same text as Sheet002!Z5 Z28 : =Sheet001!Z28 Z29 : =Sheet001!Z29
All other cells in Sheet003!Z5:Z170 would be empty. I.e., same as in Sheet002.
Instead, I get
Z5 : the same text as Sheet002!Z5 Z6 : the result of formula in Sheet002!Z29
All other cells in Sheet003!Z5:Z170 are empty.
It is as if Excel copied only the visible range (undesired), and pasted as value (undesired). The cause for pasting as values might be related to this.
I expected this to work as if no filters were applied (moreover, a specific procedure should be applied for avoiding copying hidden/filtered cells, and this is what people usually find troublesome).
Any hints on the cause of this behavior of Excel, and how to avoid it?