Ideally your code should do the following!
1. first do an auto filter on the main sheet which contains all th
data.
This can be done by code similar to

Selection.AutoFilter Field:=ColNum, Criteria1:=FiltCriteria

2. copy the filtered data to another sheet.

Sub CopyFilter()
Dim Rng As Range, FiltRng As Range
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set FiltRng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If FiltRng Is Nothing Then
MsgBox "No Records Found"
Else
'clear report range (modify this suitably)
Worksheets("Report").Range("A4:K" & Cells(Rows.Count
2).End(xlUp).Row).Clear

'filter and copy to report sheet
Set Rng = ActiveSheet.AutoFilter.Range
Rng.Offset(1, 0).Resize(Rng.Rows.Count - 1).Copy _
Destination:=Worksheets("Report").Range("A4")
End If
'remove auto filter from main sheet
ActiveSheet.ShowAllData
End Sub



If you are not sure, do a search for Autofilter+help

--
lotu
-----------------------------------------------------------------------
lotus's Profile: http://www.excelforum.com/member.php...fo&userid=2375
View this thread: http://www.excelforum.com/showthread.php?threadid=49503