Perhaps one way which might achieve it ..
Illustrated in this sample construct:
http://www.savefile.com/files/650331
Copy Results from Autofiltered List.xls
Source table assumed in A9:E200, col headers in A9:E9
In A3:
=showfilter(A9:A200)
Note: Above requires Tom Ogilvy's ShowFilter UDF to be pre-installed.
Tom's UDF & steps to install are given below the sign-off
In A4:
=IF(ISNUMBER(SUBSTITUTE(A3,"=","")+0),SUBSTITUTE(A3,"=","")+0,SUBSTITUTE(A3,"=",""))
Array-entered** in A5:
=IF(ISNA(MATCH(1,($A$10:$A$200=$A$4)*($B$10:$B$200=$B$4)*($C$10:$C$200=$C$4)*($D$10:$D$200=$D$4)*($E$10:$E$200=$E$4),0)),"",INDEX(A10:A200,MATCH(1,($A$10:$A$200=$A$4)*($B$10:$B$200=$B$4)*($C$10:$C$200=$C$4)*($D$10:$D$200=$D$4)*($E$10:$E$200=$E$4),0)))
**Press CTRL+SHIFT+ENTER to confirm the formula,
instead of just pressing ENTER
Select A3:A5, copy across to E5.
A5:E5 will return the required results from the top row in the filter.
Tom Ogilvy's UDF
'---- begin vba -----
Public Function ShowFilter(rng As Range)
Dim filt As Filter
Dim sCrit1 As String
Dim sCrit2 As String
Dim sop As String
Dim lngOp As Long
Dim lngOff As Long
Dim frng As Range
Dim sh As Worksheet
Application.Volatile
Set sh = rng.Parent
If sh.FilterMode = False Then
ShowFilter = "No Active Filter"
Exit Function
End If
Set frng = sh.AutoFilter.Range
If Intersect(rng.EntireColumn, frng) Is Nothing Then
ShowFilter = CVErr(xlErrRef)
Else
lngOff = rng.Column - frng.Columns(1).Column + 1
If Not sh.AutoFilter.Filters(lngOff).On Then
ShowFilter = "No Conditions"
Else
Set filt = sh.AutoFilter.Filters(lngOff)
On Error Resume Next
sCrit1 = filt.Criteria1
sCrit2 = filt.Criteria2
lngOp = filt.Operator
If lngOp = xlAnd Then
sop = " And "
ElseIf lngOp = xlOr Then
sop = " or "
Else
sop = ""
End If
ShowFilter = sCrit1 & sop & sCrit2
End If
End If
End Function
'-- end vba --
To implement the UDF:
Press Alt+F11 to go to VBE
Click Insert > Module
Copy and paste the UDF (below) into the white space on the right [everything
within the dotted lines ("begin vba" to "end vba")]
Press Alt+Q to get back to Excel
Then in Excel, we can use Tom's UDF as, eg in A3: =showfilter(A9:A200)
