rfletcher35
Active Member
- Joined
- Jul 20, 2011
- Messages
- 300
- Office Version
- 365
- Platform
- Windows
I am currently using a autofilter in my vba code to select a day range. I need to change this code to select current month
However even if I change the cell format to MMMM it doesn't register the word i.e. "December" in this case so I can filter on it.
As you can see below I am using CurMonth as my range
In Cell C2 I have the word December as formatted as date using custom MMMM
Dim rng365 As Range
Dim CurMonth As Range
Sheets("Results Sheet").Select
Range("P5:BL3000").Select
Selection.ClearContents
' 365 - 1 Year of Work No Absence
Set CurMonth = Range("C2")
With Sheets("MPR Data")
'.Range("$T$4:$AD$4000").AutoFilter Field:=1, Criteria1:="=365", Operator:=xlFilterValues
.Range("$T$4:$AE$4000").AutoFilter Field:=12, Criteria1:=CurMonth.Value, Operator:=xlFilterValues
On Error Resume Next
Set rng365 = .Range("T5:AE4000").SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
Can anyone help with this please?
Thanks
Fletch
However even if I change the cell format to MMMM it doesn't register the word i.e. "December" in this case so I can filter on it.
As you can see below I am using CurMonth as my range
In Cell C2 I have the word December as formatted as date using custom MMMM
Dim rng365 As Range
Dim CurMonth As Range
Sheets("Results Sheet").Select
Range("P5:BL3000").Select
Selection.ClearContents
' 365 - 1 Year of Work No Absence
Set CurMonth = Range("C2")
With Sheets("MPR Data")
'.Range("$T$4:$AD$4000").AutoFilter Field:=1, Criteria1:="=365", Operator:=xlFilterValues
.Range("$T$4:$AE$4000").AutoFilter Field:=12, Criteria1:=CurMonth.Value, Operator:=xlFilterValues
On Error Resume Next
Set rng365 = .Range("T5:AE4000").SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
Can anyone help with this please?
Thanks
Fletch