|
=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 |
=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 |
=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 |
=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 |
=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 |
| All times are GMT +1. The time now is 10:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com