Cannot copy-paste from-to ranges with filtered rows

Question

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

=Sheet001!A6

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?

Answer 1

There is no easy way around this. First of all, I can't imagine why you need 115 of the same tabs that are filtered slightly differently. Generally, it's bad design if a small task like this could take you ages.

I can't see any quick way for you to complete this. But the following might be useful:

http://office.microsoft.com/en-gb/excel-help/edit-data-on-multiple-worksheets-HP001108262.aspx

If you edit multiple tabs at the same time (ie tab002:tab115) and add a formula =tab001!A100 to tab002!A100 it will appear on every tab002:tab115 in cell A100 - which is part of what you are asking for. However, your filters will not recognise it as part of the filtered range so all filters will need to be reapplied.