| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
=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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
=today() - 1
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
=today() - 1
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
=today() - 1
from
Criteria1:=Format(Date - 1, "mm/dd/yyyy") to Criteria1:=Format(Date - 1, "mm/dd/yyyy h:mm") "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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
=today() - 1
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| =TODAY() | Excel Discussion (Misc queries) | |||
| Before today | Excel Discussion (Misc queries) | |||
| IF TODAY equals date in cell A10, or if TODAY is beyond that date | Excel Worksheet Functions | |||
|
=IF(OR(TODAY() |
Excel Discussion (Misc queries) | |||
| VBA - Today() not available in VBA? | Excel Programming | |||
![fgwiii[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
