Copy/paste filtered column in Excel - error message

Yes, I agree this is stupid. You can do it using the 'fill handle' - select the cells you want to copy and drag the fill handle across to copy them into the next column. This only works for a contiguous selection, and only if you're copying into the adjacent column. But presumably you could always move your column temporarily. I can't see any other way to do it without recourse to VBA.

You have found one of Excel's many "got ya's".

You have to do it the other way around. Copy/paste everything, filter out what you want to keep then delete the contents.

Alternatively, you could create a macro to do it in one step.

I know this is an old question, however I just wanted to give an answer for anyone that may need to do this (and their columns are not next to one another, so they're unable to use the answer provided by @benshepherd).

Once your data is filtered, in the first visible row of column C you can reference the cell in column B.

=B2

You can then drag this down your column. Unfilter, copy and then right click -> paste special -> values. Excel keeps all existing data that may be in cells which are hidden by the filter.

You can also do this by copying as normal and using the paste special "values" function. However, you must paste on to a completely separate excel session for this method to work. You can then copy from the new session back to the original session.