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
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.