locked
Pasting Columns while spreadsheet is filtered? RRS feed

  • Question

  • I have a large number of data in my excel matrix and I would like to be able to filter the data by a certain criteria, copy & paste a column while filtered, without it pasting the information into the unfiltered rows.

    What happens now is that whenever I copy a column and the matrix is filtered, it pastes the information into the rows that are not visible, messing up the information non-visible rows.  Please help!  Thank you.

    Wednesday, March 21, 2012 6:06 PM

Answers

  • This should help you get your issue straightened out:

    http://www.mrexcel.com/forum/showthread.php?t=85288

    • Proposed as answer by Dummy yoyo Monday, March 26, 2012 8:36 AM
    • Marked as answer by Bruce Song Wednesday, April 4, 2012 7:05 AM
    Wednesday, March 21, 2012 6:41 PM

All replies

  • This should help you get your issue straightened out:

    http://www.mrexcel.com/forum/showthread.php?t=85288

    • Proposed as answer by Dummy yoyo Monday, March 26, 2012 8:36 AM
    • Marked as answer by Bruce Song Wednesday, April 4, 2012 7:05 AM
    Wednesday, March 21, 2012 6:41 PM
  • You can't copy that way.

    Suppose you have filtered A column. A1,A3 is visible and you want to copy the value of A1 and A3 in B1,B3 correspondingly.Simply type =A1 in B1 and copy that down the B3....

    Thursday, March 22, 2012 10:55 AM
    Answerer
  • Hi David,

    Welcome to the forum!

    How is it going with the problem currently?

    Please refer to the following steps:

    1. Select the cells that you want to copy.
    1. On the Home tab, in the Editing group, click Find & Select, and then clickGo To.
    1. In the Go To dialog box, click Special.
    2. Under Select, click Visible cells only, and then click OK.
    3. On the Home tab, in the Clipboard group, click Copy .     
    1. Select the upper-left cell of the paste area.

        NotE: To move or copy a selection to a different worksheet or workbook, click another worksheet tab or switch to another workbook, and then select the upper-left cell of the paste area.

    1. On the Home tab, in the Clipboard group, click Paste .

    Reference From: Copy visible cells only
    http://office.microsoft.com/en-us/excel-help/copy-visible-cells-only-HA010244897.aspx 

    Please feel free to let us know if you need any help.

    Have a nice day.

    yoyo


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us


    • Edited by Dummy yoyo Monday, March 26, 2012 8:44 AM
    Monday, March 26, 2012 8:37 AM
  • I suppose the solution will only be applicable if the Paste area is unfiltered.In the post David Mentioned that he wants copy the filtered cells on a different column of same fileterd area.

    For Example :The area is A1:B8 and he has filtere and visible cells are A2,A4 then he wants the data of A2,A4 be pasted on B2,B4 resepectively.

    As per above solution the data will be pasted on B2,B3.

    Pls let me know If I am missing something.

    Monday, March 26, 2012 9:11 AM
    Answerer
  • Using Advance Filters you can filter and copy the data to the same worksheet.  Yo can either do this manually or in VBA

    From the menu

    Data - Filter - Advance

    Then click Copy To Another Location

    Advanced filter need to have Autofilter turned off.  The the list range can only be a simgle column.  the criteria can be a rangge in the worksheet or in a formula.


    jdweng

    Monday, March 26, 2012 12:07 PM