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