Try changing this line:

Set RngToCopy = .Resize(.Rows.Count - 1, 1).Offset(1, 0)

to

Set RngToCopy = .offset(1,-7).Resize(.Rows.Count - 1, 6)

Since it's filtering by CQ, the .offset(1,-7) takes you back to column CJ, but
down a row from the header.

The .resize(.rows.count-1,6) says to make the range 6 columns wide (CJ:CO), but
not include any extra row at the bottom.



Scott wrote:

This does filter up to the last cell but I would like to copy CJ16:CO16 down
as it currently copies CQ down. I would still like to have it look to CQ for
the last row and then copy CJ16:CO16 down based on the last CQ data. Then
copy it to the "B" sheet as it does to A3:F3 down. I know it is a little
confusing sorry, don't know much about this (if any).

Thank You

"Dave Peterson" wrote:

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


--

Dave Peterson