PDA

View Full Version : Copy Paste of Filtered data with hidden columns



Asi
11-18-2009, 03:28 AM
Hi

I have two Sheets with the same table formats. Source Sheet & Destination Sheet. Both Sheets have hidden columns (as I said same tables in both so same columns are hidden).
In the Source Sheet I filter the data and then select the filtered rows and copy. In the destination sheet I select the first cell and paste.
The problem is that the data is shifted to the right because of the hidden column. The copy operation copied ONLY the visible columns (without the hidden ones) and The Paste operation pasted it into the hidden columns.

If I do the same thing without filtering - there is no problem and all columns are copied into the correct place including the hidden ones.

This is how Excel acts. The question is - Is there a way to copy all columns including hidden (after filtering) or do I need to unhide -> Copy -> hide ?

Thanks
Asi

p45cal
11-18-2009, 05:59 AM
Playing with this I've come to the conclusion that the easiest way is temporarily to unhide the Source columns to copy. The destination columns can remain hidden.
If, as you say, the same columns should be hidden in both sheets, you can restore the hidden columns in the Source sheet by referring to the hidden properties of the columns in the destination sheet with the likes of:
For Each c In Sheets("Dest").UsedRange.Columns
Sheets("Source").Columns(c.Column).Hidden = c.Hidden
Next c

Asi
11-18-2009, 06:05 AM
Thanks for you help