Hello,
I am trying to use autofilter to only show records with a date set for during the next 3 weeks.
Two problems:
1. If Date+21 is during the following month, VBA/autofilter appears to try and ignore information saved in local UK date format dd/mm/yyyy and tries to use USA date format mm/dd/yyyy.
2. It does not display data that meets the filter criteria.
Can anyone help?
Example Data
Code I am Using
Many thanks,
Mark
I am trying to use autofilter to only show records with a date set for during the next 3 weeks.
Two problems:
1. If Date+21 is during the following month, VBA/autofilter appears to try and ignore information saved in local UK date format dd/mm/yyyy and tries to use USA date format mm/dd/yyyy.
2. It does not display data that meets the filter criteria.
Can anyone help?
Example Data
due within 21 days.xls | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | Task | DueDate | ||||
2 | A | 17/03/2003 | ||||
3 | B | 18/03/2003 | ||||
4 | C | 19/03/2003 | ||||
5 | D | 20/03/2003 | ||||
6 | E | 21/03/2003 | ||||
7 | F | 22/03/2003 | ||||
8 | G | 23/03/2003 | ||||
9 | H | 24/03/2003 | ||||
10 | I | 06/04/2003 | ||||
11 | J | 07/04/2003 | ||||
12 | K | 08/04/2003 | ||||
13 | L | 09/04/2003 | ||||
14 | M | 10/04/2003 | ||||
15 | N | 11/04/2003 | ||||
Sheet1 |
Code I am Using
Code:
Sub Due_within_21_days()
Dim Date_To_Use
Date_To_Use = Format((Date + 21), "dd/mm/yyyy")
Columns("A:B").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="<" & Date_To_Use, Operator:=xlAnd
'Stop
Columns("A:B").Select
Selection.Copy
Range("E1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFilter
End Sub
Many thanks,
Mark