Hi Craig,

Full code list below........this AutoFilter is killing me, Thanks for
you kind guidance.


As the suggested code runs without problem for me, and particularly bearing
in mind your experience with the second filter condition, I think that your
problems relate to peculiarities in your data configuration.

I have, therefore, already offered to look at your workbook if you want to
send it.


---
Regards,
Norman



"Craigm" wrote in
message ...

To establish the AutoFilter on row 7 (header row) I have added:
Rows("7:7").Select
Selection.AutoFilter

If I comment out the line below, I copy the AutoFilter row (Header) and
all the data.
Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1)

When I change/comment out the above line above I get the error: "Copy
method of range class failed" on this line:
rng1.Copy Destination:=destSh.Range("A1")

Full code list below........this AutoFilter is killing me, Thanks for
you kind guidance.

Respectfullt, Craigm
-------------------------------------------------------------------------------------

Public Sub Tester02 (sSheet As String)
'Copies filtered data (visible) to worksheet passed in.

Dim rng As Range
Dim rng1 As Range

Dim srcSh As Worksheet
Dim destSh As Worksheet

Worksheets(sSheet).Cells.Clear

Worksheets("DataDown").Activate

With ActiveWorkbook
Set srcSh = .Sheets("Datadown")
Set destSh = .Sheets(sSheet)
End With

'Establish the filter on row 7
Rows("7:7").Select
Selection.AutoFilter
srcSh.Cells.AutoFilter Field:=13, Criteria1:="12/31/2003",
Operator:=xlAnd, Criteria2:="<=01/01/2005"


Set rng = srcSh.AutoFilter.Range

'This is wacking off the header in row 7
Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1)


On Error Resume Next
Set rng1 = rng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If Not rng1 Is Nothing Then
rng1.Copy Destination:=destSh.Range("A1")
End If
End Sub
---------------------------------------


--
Craigm
------------------------------------------------------------------------
Craigm's Profile:
http://www.excelforum.com/member.php...o&userid=24381
View this thread: http://www.excelforum.com/showthread...hreadid=387968