Hi,
From a dropdown user selects the following date format for months (09-2016, 10-2016, 11-2016, etc.) and for years they select (2016, 2017, etc.)
I have a macro that filters for the specified date but I believe there is a more efficient way of doing this. The way i am doing this now is creating a separate column and using the Text function to format the dates appropriately and my macro pulls from that column. Please see example of table below.
I believe there is a way in my vba code where i can format the filtered date field (column O) within VBA rather than creating additionals columns. Can anyone help? Below is the code that i am currently using.


i have attached the full file if needed.
From a dropdown user selects the following date format for months (09-2016, 10-2016, 11-2016, etc.) and for years they select (2016, 2017, etc.)
I have a macro that filters for the specified date but I believe there is a more efficient way of doing this. The way i am doing this now is creating a separate column and using the Text function to format the dates appropriately and my macro pulls from that column. Please see example of table below.
I believe there is a way in my vba code where i can format the filtered date field (column O) within VBA rather than creating additionals columns. Can anyone help? Below is the code that i am currently using.


Code:
If Range("b7") = vbNullString Then 'it is blank
dataWB.Close SaveChanges:=False
Else
If timeframe = "All Months" Then
With ActiveSheet
.Unprotect "ops"
.AutoFilterMode = False
.Columns("B:AB").EntireColumn.Hidden = False
.ListObjects("CJR_TBL").Range.AutoFilter Field:=1, Criteria1:="<>"
End With
ElseIf timeframe = "2016" Then
With ActiveSheet
.Unprotect "ops"
.AutoFilterMode = False
.Columns("B:AB").EntireColumn.Hidden = False
.ListObjects("CJR_TBL").Range.AutoFilter Field:=2, Criteria1:=timeframe
End With
ElseIf timeframe = "2017" Then
With ActiveSheet
.Unprotect "ops"
.AutoFilterMode = False
.Columns("B:AB").EntireColumn.Hidden = False
.ListObjects("CJR_TBL").Range.AutoFilter Field:=2, Criteria1:=timeframe
End With
Else
With ActiveSheet
.Unprotect "ops"
.AutoFilterMode = False
.Columns("B:AB").EntireColumn.Hidden = False
.ListObjects("CJR_TBL").Range.AutoFilter Field:=1, Criteria1:=timeframe
End With
i have attached the full file if needed.