=today() - 1


G

Guest

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
 
Ad

Advertisements

J

JE McGimpsey

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
 
G

Guest

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
 
G

Guest

from
Criteria1:=Format(Date - 1, "mm/dd/yyyy")
to
Criteria1:=Format(Date - 1, "mm/dd/yyyy h:mm")
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top