Copy/paste filtered column in Excel - error message

Question

In short, I have filtered a worksheet by column A, and I want to copy/paste from column B to column C. Obviously I don't wish to copy/paste values from rows that were filtered-out here.

The above sounds ridiculously simple, right?

1. First I tried simply copy/pasting on the filtered worksheet. This appeared to select and copy only the filtered data, however pasting appeared to insert values into hidden/filtered rows - as you might expect.

2. So my initial research suggests I may wish to select the filtered data and press Alt+;, which is a shortcut key for Goto Special > Select Visible. Then just copy-paste.

   CTRL+C correctly copies the filtered data, however when I go to paste the values into another column, it pastes into hidden rows as well.

3. Okay, so perhaps I should also "Select Visible" on the cells I wish to paste into as well? Nope - that gives me the error That command cannot be used on multiple selections.

What am I doing wrong?!

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

Have you tried to Fill Right? (Highlight Columns B and C and then Ctrl + R)

This works for me.

Answer 6

A better approach might be to use an IF formula something like =if(A2='filter criteria',B2,"") in column C.

If you want static rather than dynamic data you can copy column c afterwards and paste special(values).

Answer 7

1. Copy filtered content after pressing F5 key then go to special and select visible cell only.
2. Filter the area where you want to paste that filtered copied content.
3. Now select that copied area and drag and drop using hand tool

Answer 8

I changed the format of the file from .xlsx to .xls and then that seemed to work. none of the answers above made sense or worked for me. I have a MacBook Pro. not sure if that makes a difference. I Am a super end user and have no tech experience at all.

Answer 9

I had a similar problem but found A quick solution that could help,

-First create an extra column next to all the data you want to copy

• then use the concatenate function to combine all the date you need into one cell

• now press ctrl F

• find the keyword cells you need

• press the first result in the table below and press control shift and the down arrow to select all of them

• dont close the program, but move the curser over to the highlighted cells and right click and then copy

• then open a new worksheet and press paste

• this should solve the problem :)

Answer 10

I know it is an old question but a fresh answer might be helpful to anyone that still wonders how to copy and paste multiple selections into the filtered tab.

I suggest taking the following steps which just have worked for me:

1. Filter the tab that you want to paste stuff into it.
2. use the Vlookup function to find the item (Use only for one item at this stage) on the other spreadsheet / Tab.
3. Then use a shortcut key for Goto Special > Select Visible. Then just copy-paste the Vlookup formula for that 1 item that you used, across the filtered tab.

And voilà! It copied the vlookup function into only filtered cells which searched for you item that you wanted to copy-paste!

Now just copy the column and paste the value only and you are super done.

Hope this helps.

Answer 11

Try inserting a new sheet into the file. Then Try copy pasting your data into the new sheet.

Thanks

Answer 12

none of these answers will work. what WILL work is to take the destination sheet, filter what you want to insert, put a color fill into the selection, un-filter, then sort (not filter) by color. you can paste your information on the sorted by color cells, then change the color back to the original color and re-sort by whatever method you had before.