Copy/Paste Autofiltered Data (97)

Topic

New Reply

Is it possible to copy autofiltered data from one worksheet to another and have the filters copy with the data? (I hope this makes sense). I know I could use an Advanced filter to copy the filtered data to another worksheet, but I want the filter criteria too.

Reply | Quote

Replies

According to Microsoft (q214306), you can’t copy/paste AutoFiltered data. (The KnowledgeBase item is for Excel 2000, but I suppose it is also valid for Excel 97.)

Perhaps somebody can come up with a solution in VBA; I don’t know how to get at the AutoFilter criteria.

When you copy the entire worksheet containing AutoFiltered data (instead of copying a range of cells), the AutoFilter is copied to the new sheet complete with the imposed criteria.

Reply | Quote

I copy and paste autofiltered data all the time. However, only the data which passes the filter test is copied. I am unaware how to copy the filter itself though, asside from copying a whole sheet.

Also, a single level of summarized data may be copied. I have added the “Select Visible Cells” icon to my toolbar for this purpose.

Reply | Quote

Edited by WebGenii on 10-Apr-02 23:16.

There is a way to print the aotofilter values, see http://j-walk.com/ss/excel/usertips/tip044.htm%5B/url%5D

Reply | Quote

To get at the AutoFilter criteria, turn on the macro recorder and then change some of the AutoFilter criteria.
Stop recording and then look at the code generated.
To simplify tasks, you could assign range names to the relevant cells.
You should then be able to set and retrieve the criteria through VBA

zeddy

Reply | Quote

Thanks, zeddy!

I don’t know if the original poster is still interested. Here is a crude macro that still needs a lot of work for a real-life application (especially error-handling). It asks for a source cell and destination cell. The source cell should be somewhere within the AutoFiltered table; the destination cell should be in another worksheet.

Sub CopyWithAutoFilter()
Dim rngSrc As Range, rngDst As Range
Dim af As AutoFilter, f As Filters, i As Integer
Dim FilterInfo()
Set rngSrc = Range(InputBox(“Source”))
Set rngSrc = rngSrc.CurrentRegion
Set rngDst = Range(InputBox(“Destination”))
Set af = rngSrc.Parent.AutoFilter
Set f = af.Filters
ReDim FilterInfo(1 To f.Count, 1 To 5)
For i = 1 To f.Count
If f(i).On Then
FilterInfo(i, 1) = f(i).Criteria1
FilterInfo(i, 3) = f(i).Operator
If f(i).Operator 0 Then
FilterInfo(i, 2) = f(i).Criteria2
End If
End If
FilterInfo(i, 4) = f(i).On
Next i
rngSrc.Copy rngDst
Set rngDst = rngDst.CurrentRegion
For i = 1 To f.Count
If f(i).On Then
If f(i).Operator 0 Then
rngDst.AutoFilter i, f(i).Criteria1, f(i).Operator, f(i).Criteria2
Else
rngDst.AutoFilter i, f(i).Criteria1
End If
End If
Next i
End Sub

Reply | Quote

Wow! that looks interesting!
I shall save this for later use.

Thanks
zeddy

Reply | Quote