Pasting into a Filtered Data Set

March 24, 2009 - by Bill Jelen

Joe asks if there is a way to do a filtered paste operation in Excel.

Say you have 1000 records on Sheet1. You filter that data set to show you only the records with ProdID = Z. Select the data set and paste to Sheet2. Nicely, Excel brings only the visible records, so you have a contiguous block of cells. Perhaps you do some transformations to those records and now you need to paste it back into the original data set. Excel does NOT offer any easy way to do this. The macro in this video will assist with the process. I recorded Learn Excel Podcast episode 977 showing a macro to help with this action. Here you will find the video and the code used in the Excel workbook.

Watch Video

Copy this code

I adjusted the code from the video a little. This assumes that you will copy the heading row and the filtered records from Sheet1 to Sheet2:

Sub PasteBack()
    ' Assumes the Filterered Sheet1 is active
    Set DataToCopy = Sheet2.Range("A1").CurrentRegion
    n = 2 ' index for range to copy from Sheet2 one row at a time, skipping headers
    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row ' row 1 is header
        If Not Rows(i).Hidden Then
            DataToCopy.Offset(n - 1).Resize(1).Copy Cells(i, 1)
            n = n + 1
            If n > DataToCopy.Rows.Count Then Exit Sub
        End If
    Next i
End Sub

A Solution Without Macros

After the first video aired, Gary sent in a new solution using Paste Skip Blanks. Watch that video here:.