MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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:.

Bill Jelen is the author / co-author of
Microsoft Excel 2019 Inside Out

Dive into Microsoft Excel 2019–and really put your spreadsheet expertise to work. This supremely organized reference packs hundreds of timesaving solutions, tips, and workarounds–all you need to make the most of Excel’s most powerful tools for analyzing data and making better decisions.