Oops. There's a bug in that code if there's only headers visible.

Use this instead:

Option Explicit
Sub testme03()

Dim FromWks As Worksheet
Dim ToWks As Worksheet
Dim RngToFilter As Range
Dim RngToCopy As Range

Set FromWks = Worksheets("A")
Set ToWks = Worksheets("B")

With FromWks
'remove any existing filter
.AutoFilterMode = False

Set RngToFilter = .Range("CQ15", .Cells(.Rows.Count, "CQ").End(xlUp))

RngToFilter.AutoFilter field:=1, Criteria1:="<"

If .AutoFilter.Range.Columns(1).Cells.SpecialCells(xl CellTypeVisible) _
.Cells.Count = 1 Then
'only the headers are visible, nothing to copy???
Else
With RngToFilter
'skip the header and resize to avoid an
'extra row at the bottom
Set RngToCopy = .Resize(.Rows.Count - 1, 1).Offset(1, 0)
End With

'in newer versions of excel (xl97 and higher), the default is to
'copy the visible cells
RngToCopy.Copy
ToWks.Range("a3").PasteSpecial Paste:=xlPasteValues

End If
End With

End Sub

Notice the .copy stuff has been moved into the else portion--where I know
there's data to copy.


Dave Peterson wrote:

<<snipped