How to Copy and Paste Data in Filtered Cells

I was recently working on an Excel project, where I was required to filter a column for a particular item and copy data from the corresponding cells and paste them in another filtered column. If you have tried doing this before, you will know that copying data from a filtered column and pasting them in another filtered column will result in all the cells hidden beneath the filter getting filled up with the copied data as well. Luckily, Excel allows you to paste data only in filtered cells using macros. In this tutorial, I’ll show you how to copy and paste data in filtered cells in Excel 2010.

If you want to copy a single value across filtered cells, just select the range of cells you want to paste the value in, press Crtl+G. Then select Special and choose Visible cells only and click OK. Now paste the data. This will paste the single value across the filtered cells.

If you want to copy multiple values from filtered cells and paste them in another column of filtered cells, a macro should do the trick.

In the graphic below, I have filtered out the product Alice Mutton. I want to copy the data in Column C from visible Cells C8 to C 144 and paste them in visible cells D2 to D121.

To do this, I setup a macro to help me do this the lazy way.

Code:

Sub Filtered_Cells()
Dim from As Range

Set from = Application.InputBox(“Select range to copy selected cells to”, Type:=8)

from.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Call Copy_Filtered_Cells
End Sub

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

How to Use the Macro

1. Copy the code.
2. Open Excel 2010.
3. Press Alt + F11 to enter the Visual Basic Editor.
4. If necessary, press Ctrl + R to show the Project Explorer.
5. Right-click desired file on left (in bold).
6. Choose Insert -> Module.
7. Paste code into the right pane.
8. Press Alt + Q to close the VBE.
9. Save the workbook in the XLSM format.
10. Select the Developer tab. (If you have a problem displaying the Developer tab, please read our post How to Activate the Developer Tab in Excel 2010.

How to Copy and Paste Data in Filtered Cells in Excel 2010

1. Ensure that the workbook is saved in the XLSM format.

2. Filter the data according to your requirement. For this example, I have filtered Alice Mutton.

3. On the Developer Tab, in the Code group, click Macros.

4. In the Macro dialog box, select the [WorksheetName]_Filtered_Cells macro and click Run.

5. The Input message box is displayed. Select the range of cells you want to copy by dragging across the cells. The values will be automatically updated in the Input box. Click OK.

6. The Input message box is displayed again. This time select the range of cells you want to paste the data in. In this example it is cell D2 : 121.

The data is instantly copied to the adjacent cells.

For your convenience I have setup a blank Excel sheet with the macro incorporated in the worksheet. Just transfer your data to this sheet and perform the copy and paste functions.

Download the Macro Sheet to Copy and Paste in Filtered Cells. (Right-click as save target or Save Link as )

Don’t forget to follow us on Twitter or join our Facebook fan page to get tutorials, updates and how-to articles on Microsoft, Google and Apple products.