Excel 2007 - Problem Copying and Pasting From An Auto-Filtered List

[Andrew.D.Robertson]

Hi,

I am hoping that someone can help me with two problems I am having in
Excel 2007. The functionality of copying and pasting to and from
filtered lists seems to be different from what I experienced in Excel
2003.

Problem 1) Copying (with Ctrl-C) from one column in a filtered list
into another column in the filtered list (with Ctrl-V)

When I copy with Ctrl-C it selects the cells in the list that are
visible (which is what I want) but when I paste those over another
column in the same filtered list (I have not changed the filter
settings) it pastes the data over cells that are not visible if there
is a gap between the rows that are visible. For example - if I copied
5 cells in a column into the column next to it, but there were 4 rows
that were not visible after the first visible row - Excel would copy
this data over the one visible cell and the 4 non-visible cells. But
what I want it to do is copy the data over only the first 5 visible
cells. I hope that makes sense.

Problem 2) Dragging a data value down a column in a filtered list in
order to copy over the other data entries in that column

When I take a data value and drag is into all the cells below it in a
filtered list Excel copies this value into all the non-visible cells
as well. I only want these to be copied into the visible cells and I
recall Excel 2003 doing this.

If you can help with either of these problems that would be much
appreciated. I am hoping this is just a setting issue - but I have not
found any settings that would fix this. It's a major productivity
setback for me.

Thanks,

Andrew

 

[Pete_UK]

I use XL2000 and I'm sure it works in the same way. One way around
your first problem is to enter a formula in the first visible cell in
the destination column:

=source_cell

(i.e. type = then just click on the cell in the source column). Then
copy this formula down - rather than drag, click <copy> (or CTRL-C)
then highlight the visible cells below this and press Enter. This also
applies to your second problem.

Hope this helps.

Pete

 

[Jim Rech]

I believe your recollection about Excel 2003 and its pasting behavior is not
quite right, Andrew. Excel 2003 (and 2007) are smart about selecting/copy
only the visible rows of a filtered range/list/table but neither is smart
about pasting the result into a filtered range.

Re the handle-dragging fill down issue, well, I've tried it several times
and ways and I find that Excel 2007 fills into just the visible cells. Big
help, huh?<g>

--
Jim
| Hi,
|
| I am hoping that someone can help me with two problems I am having in
| Excel 2007. The functionality of copying and pasting to and from
| filtered lists seems to be different from what I experienced in Excel
| 2003.
|
| Problem 1) Copying (with Ctrl-C) from one column in a filtered list
| into another column in the filtered list (with Ctrl-V)
|
| When I copy with Ctrl-C it selects the cells in the list that are
| visible (which is what I want) but when I paste those over another
| column in the same filtered list (I have not changed the filter
| settings) it pastes the data over cells that are not visible if there
| is a gap between the rows that are visible. For example - if I copied
| 5 cells in a column into the column next to it, but there were 4 rows
| that were not visible after the first visible row - Excel would copy
| this data over the one visible cell and the 4 non-visible cells. But
| what I want it to do is copy the data over only the first 5 visible
| cells. I hope that makes sense.
|
| Problem 2) Dragging a data value down a column in a filtered list in
| order to copy over the other data entries in that column
|
| When I take a data value and drag is into all the cells below it in a
| filtered list Excel copies this value into all the non-visible cells
| as well. I only want these to be copied into the visible cells and I
| recall Excel 2003 doing this.
|
| If you can help with either of these problems that would be much
| appreciated. I am hoping this is just a setting issue - but I have not
| found any settings that would fix this. It's a major productivity
| setback for me.
|
| Thanks,
|
| Andrew
|