It looks like its failing because of the field number. It is a typo 10
should be 1.

"fgwiii" wrote:

This is failing (I think) because the values in the date field =
5/16/2007 0:00
5/15/2007 0:00
5/14/2007 0:00
5/16/2007 0:00
Would a different syntax be used in this case?

Thank you

Fred
"JE McGimpsey" wrote:

One way:

With ActiveSheet.UsedRange
.AutoFilter
.AutoFilter _
Field:=10, _
Criteria1:=Format(Date - 1, "mm/dd/yyyy")
.Copy
End With
Workbooks.Add
With ActiveSheet
.Paste
.UsedRange.EntireColumn.AutoFit
End With

In article ,
fgwiii wrote:

I have a macro with the following code that I am trying to run so that it
will select the date before today. I tried inserting <=today() - 1
Unfortunately the macro bombs.

Cells.Select
Selection.AutoFilter
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
Selection.AutoFilter Field:=10, Criteria1:=today() - 1

ActiveCell.SpecialCells(xlLastCell).Select
Range("A1:J14597").Select
Range("J14597").Activate
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Cells.Select
Cells.EntireColumn.AutoFit
End Sub

Thanks for your help.

Fred