VBA Autofilter based of Today's Date

[stronik814]

Hi,

I'm trying to auto - filter column 2 "Frequency" by today's current date, Cell A1 & by the word "Daily". I'm trying to make this happen automatically once the user opens the workbook.

For example, if the user opens the workbook on a Wednesday, the Frequency table should be filtered to only show Wednesday & Daily. I looked on other forums and posts and have not been able to figure it out. Any help would be greatly appreciated, thank you!

A1	=today() -> Tuesday Oct 7, 2014.
	Line Item	Frequency
	1	Tuesday
	2	Daily
	3	Daily
	4	Wednesday
	5	Wednesday
	6	Daily
	7	Monday
	8	Tuesday & Thursday
	9	Monday
	10	Tuesday
	11	Tuesday

<TBODY>
</TBODY>

 

[venkat1926]

can we not have instead of


8 Tuesday & Thursday
two rows
8 Tuesday
8 Thursday

 

[venkat1926]

agreeing to my caveat data is like this in sheet1


Line Item Frequency
1 Tuesday
2 Daily
3 Daily
4 Wednesday
5 Wednesday
6 Daily
7 Monday
8 Tuesday
9 Monday
10 Tuesday
11 Tuesday
8 Thursday

now open vbeditor-goto THIS FILE, right click "thisworkbook"
and in the window that opens copy tis EVENT CODE

Private Sub Workbook_Open()
Dim dday As String, r As Range
dday = WeekdayName(Weekday(Date), False)
Worksheets("sheet2").Cells.Clear
With Worksheets("sheet1")
Application.EnableEvents = False
Set r = .Range("A1").CurrentRegion
r.AutoFilter field:=2, Criteria1:=dday
r.SpecialCells(xlCellTypeVisible).Copy
With Worksheets("sheet2")
.Range("A1").PasteSpecial
End With
.AutoFilterMode = False
End With
MsgBox "macro done. see sheet2"
Application.CutCopyMode = False
Application.EnableEvents = True
End Sub

Now save the file as macro enabled excel file
close the file
now open the file
see what happens.

 

[stronik814]

Thank you for your replies! I tried copying the code, but it pastes the data filtered with only "Wednesday" filtered but is missing the "daily". Also it filters the data and copies and pastes it to another sheet, which I can see in the code. I need the original data to filter, just as if I were to manually filter it by clicking "Wednesday" & "Daily" in the filter column, but would like it to do it automatically if I could based of today's date. -Thank you!

 

[venkat1926]

I thought copying the FILTERED DATA to another sheet so that you can, if you want, manipulate for any purpose instead of vitiating the look of original data in sheet1

anyhow as you wish

the old event code may be DELETED (REMOVED) AND IN THAT PLACE COPY THIS MODIFIED EVENT CODE. save the file and closes and again open

Private Sub Workbook_Open()
Dim dday As String, r As Range
dday = WeekdayName(Weekday(Date), False)
'Worksheets("sheet2").Cells.Clear
With Worksheets("sheet1")


Application.EnableEvents = False
.AutoFilterMode = False
Set r = .Range("A1").CurrentRegion
'r.AutoFilter field:=2, Criteria1:=dday


r.AutoFilter field:=2, Criteria1:=dday, Operator:=xlOr, Criteria2:="Daily"
'r.SpecialCells(xlCellTypeVisible).Copy
'With Worksheets("sheet2")
'.Range("A1").PasteSpecial
End With
'.AutoFilterMode = False
'End With
MsgBox "macro done. "
'Application.CutCopyMode = False
Application.EnableEvents = True
End Sub