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