 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / May 2007=today() - 1
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 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 > 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> [quoted text clipped - 22 lines] > > Fred 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 > One way: > [quoted text clipped - 37 lines] > > > > Fred from Criteria1:=Format(Date - 1, "mm/dd/yyyy") to Criteria1:=Format(Date - 1, "mm/dd/yyyy h:mm") > This is failing (I think) because the values in the date field = > 5/16/2007 0:00 [quoted text clipped - 48 lines] > > > > > > Fred It looks like its failing because of the field number. It is a typo 10 should be 1. > This is failing (I think) because the values in the date field = > 5/16/2007 0:00 [quoted text clipped - 48 lines] > > > > > > Fred |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.