How to paste in filtered cells only?

I've searched for a way to paste non-contiguous copied data into (non-contiguous) visible only cells for a number of years now, and although there have been some solutions posted that would require writing some code for a macro, etc., no solution has been presented by MS. Ultimately, it appears that there are a lot of people who would find a Paste Special option of "Paste into visible cells only" very useful. I can't understand why this has not been added by MS.

I've searched for a way to paste non-contiguous copied data into (non-contiguous) visible only cells for a number of years now, and although there have been some solutions posted that would require writing some code for a macro, etc., no solution has been presented by MS. Ultimately, it appears that there are a lot of people who would find a Paste Special option of "Paste into visible cells only" very useful. I can't understand why this has not been added by MS.

This does exist in Excel (despite being hidden or not immediately intuitive).

After you have selected the data you need to copy into the non-contiguos cells that have been filtered:

• In Excel 2007 and earlier: Edit-> Go To -> Special -> Visible Cells Only will select the data, then you do a Paste Special Values
• In Excel 2010 and above: in the Home tab Find and Select -> Go to Special -> Visible Cells onlywill select the data, then you do a Paste Special Values

The easier way to paste into only the visible/filtered cells is by highlighting the cells you'd like to paste into and then using CTRL + D.

Hope this helps!

In sheet1 I have a list of data with 100 records A1:A100, In sheet2 I have a list of 10 records A1:A10. When I filter Sheet1 by color I get 10 records. I would like to paste the 10 records from sheet2 into the yelow filtered cells only. but when I do it, it overides all other cells in between. I tried to select visible cells only before pasting but it didn't help.

I found an answer by Bob Umlas, Excel MVP http://social.answers.microsoft.com/Forums/en-US/excslcreate/thread/ebec7959-60bd-4903-ad8e-391f2f8d8479 

Select the cells to copy, run this code:

Sub Copy_Filtered_Cells()

    Set from = Selection

    Set too = Application.InputBox("Select range to copy selected cells to", Type:=8)

    For Each Cell In from

        Cell.Copy

        For Each thing In too

            If thing.EntireRow.RowHeight > 0 Then

                thing.PasteSpecial

                Set too = thing.Offset(1).Resize(too.Rows.Count)

                Exit For

            End If

        Next

    Next

End Sub

it seems to be working, thanks

Thank you for taking the time to post this solution. This worked for me.

NOTE: Before following the steps below save a copy of your file in-case you need to revert back.

For less savvy users... to use the solution above follow these steps (Excel 2010):

1. Enable Developer console in Excel by navigating to File > Options,  then select the Customize Ribbon tab,  on the right hand side check the box next to Developer.
2. Click on the Developer tab on the top ribbon and then click Visual Basic.
3. Once in Visual Basic double click the Sheet you're working with in Excel and paste the following code in the box that opens:

Sub Copy_Filtered_Cells()

    Set from = Selection

    Set too = Application.InputBox("Select range to copy selected cells to", Type:=8)

    For Each Cell In from

        Cell.Copy

        For Each thing In too

            If thing.EntireRow.RowHeight > 0 Then

                thing.PasteSpecial

                Set too = thing.Offset(1).Resize(too.Rows.Count)

                Exit For

            End If

        Next

    Next

End Sub

Note: This is now saved as a macro and future uses can be done by pressing Alt+F8 and using the Copy_Filtered_Cells macro.

4. Close the Visual Basic window.
5. Ensure the data you're copying is OUTSIDE of the filtered rows (e.g at the bottom of the spreadsheet).
6. Select the data you're copying, then press Alt+F8 to bring up Macros, then run Copy_Filtered_Cells. Immediately following this an Input box will come up and ask you for your range you would like to copy the cells to (the cells should already be filtered) select the cells or type in the range and hit OK.

Now when you un-filter your cells the data should only be copied into the cells that were previously filtered.

Instead of filtering, can you sort your data so that the range to be overwritten

is contiguous?

If no, then I think you'll have to paste piece by piece by piece.

sparks555 wrote:

In sheet1 I have a list of data with 100 records A1:A100, In sheet2 I have a list of 10 records A1:A10. When I filter Sheet1 by color I get 10 records. I would like to paste the 10 records from sheet2 into the yelow filtered cells only. but when I do it, it overides all other cells in between. I tried to select visible cells only before pasting but it didn't help.

--

Dave Peterson