We're back to the same problem I had earlier, Dave. Whenever I try to use
"dim [anything] as [object or range], [anything] has a value of "Nothing",
which the Set statement doesn't seem to overcome, and (so?) I get an "object
required" run-time error (#424).

Here's the actual code I'm using:

Sub TwoMonthsExpired()
'
' TwoMonthsExpired Macro
' Macro recorded 11/17/2007 by Ross B. Lampert
' This macro selects all members who are listed as "Expired Two Month Ago"
' and copies them from the "Updates" worksheet onto the "Expiring"
worksheet.

Dim rngtocopy As Range
With Worksheets("Updates")
.AutoFilterMode = False 'Turns off any existing autofilter
.Range("A1").CurrentRegion.AutoFilter Field:=16, Criteria1:="EXPIRED TWO
MONTH AGO"
With .AutoFilter.Range
If Columns(1).Cells.SpecialCells(xlCellTypeVisible).C ells.Count = 1
Then
' This skips the case where the AutoFilter produces no rows
Else
' This line is supposed to remove the header row and start the
copy with the first data row
Set rngtocopy = .Resize(.Rows.Count - 1, .Columns.Count.Offset(1))
' Problems above: rngtocopy = Nothing and Run-time error
424--"Object required" here.

' Copies the data rows onto the first blank row of the
"Expiring" tab
rngtocopy.Copy _

Destination:=Worksheets("Expiring").Range("A1").Cu rrentRegion.End(xlDown).Offset(1)
End If
End With
End With


"Dave Peterson" wrote:

maybe...

dim RngToCopy as range
with worksheets("somesheetnamehere")
.autofiltermode = false 'turn off any existing autofilter
.range("a1").currentregion.autofilter Field:=19, Criteria1:="<"
with .autofilter.range
if .columns(1).cells.specialcells(xlcelltypevisible). cells.count = 1 then
'only headers are visible, so skip it
else
'"remove" a row and come down 1 row
set rngtocopy = .resize(.rows.count-1,.columns.count) _
.offset(1,0)
rngtocopy.copy _
destination:=Worksheets("E-Mails").Range("A2")
end with
End with

(Untested, uncompiled. Watch for typos!)

--

Dave Peterson