Advance Filter: Filter current month date range?

[Glitch5618]

Using excel 2007.

I have what may be a simple question with a simple answer. I can only hope..

What I'm trying to do is filter a large list of data by the current month. I'm aware that for AUTOFILTER its a simple bit of code like the following:

'Filter for current month
With dWS
    .AutoFilterMode = False
    With fRng
        .AutoFilter Col, Criteria1:=xlFilterThisMonth, Operator:=xlFilterDynamic
        On Error Resume Next
    End With
End With

However the issue is I'm not using an autofilter. I have a large data set and I need to be able to do the same thing as the code above but with my advanced filter code below:

'If date range is entered for outlier data
If dVer = True And f.chkOutlier.Value = True Then
    GenOutlierData
    Application.ScreenUpdating = False
End If


'Copy Column Headers from data sheet to filter sheet
dWS.Activate
LC = dWS.Cells(1, Columns.Count).End(xlToLeft).Column
Set dRng = dWS.Range(Cells(1, 1), Cells(1, LC)) 'Find last column within escalation data
dRng.Copy Destination:=fWS.Range("A2")
fWS.Cells.ClearFormats


'Create Criteria Range Headers, MUST match escalation data headers exactly
fWS.Range("AA3").Value = "Agent Name"
fWS.Range("AB3").Value = "Manager Name"
fWS.Range("AC3").Value = "LOB"
fWS.Range("AD3").Value = "BAM"
fWS.Range("AE3").Value = "Site"
fWS.Range("AF3").Value = "Nest/Prod" 'Nesting/Production
fWS.Range("AG3").Value = "Date" 'Start Date
fWS.Range("AH3").Value = "Date" 'End Date


'Set CopyToRange for Advanced fitler
fWS.Activate '''fWS sheet MUST be active before advanced filter is ran for it to work!
Set fRng = fWS.Range(Cells(2, 1), Cells(2, LC))


'Advanced filter
On Error GoTo EndCode
dWS.Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Range("AA3:AH4"), copytorange:=fRng, Unique:=False

As it stands right now my code is rather complicated to do what I think should be a simple few lines of code (I have not included all the code). Right now I have to apply an autofilter for the current month to the dataset, copy the results to a temp worksheet, and then apply my advanced filter.

I imagine what one would need to do is to have the system put the current date in cell AH3 (end date) and somehow have the program return the first day of whatever current month it is and convert that into a date placed in cell AG3 (start date). I'm just at a loss on how to do that. I would love it if someone could help me with this issue. It would cut down on my programs run time tremendously. Thanks in advance for anyone who takes the time to help me with this issue.

 

[mole999]

Have you considered a helper column, that just generates month and year i.e =TEXT(B9,"mm yy") or similar

actually in AG3 =TEXT(AH3,"mm yy")

 

[Glitch5618]

Well the data set I'm filtering already has a date column in the format of MM/DD/YYYY

I'm not sure how that would give me the intended results though.

 

[Glitch5618]

It appears I've found an answer to my question myself. For anyone who may run into this issue here is the solution.

'Create Criteria Range Headers, MUST match escalation data headers exactly
fWS.Range("AA3").Value = "Agent Name"
fWS.Range("AB3").Value = "Manager Name"
fWS.Range("AC3").Value = "LOB"
fWS.Range("AD3").Value = "BAM"
fWS.Range("AE3").Value = "Site"
fWS.Range("AF3").Value = "Nest/Prod" 'Nesting/Production
fWS.Range("AG3").Value = "Date" 'Start Date
fWS.Range("AH3").Value = "Date" 'End Date


'If dver is false then code will filter for current month
If dVer = False Then
    fWS.Range("AG4").Value = ">=" & Month(Date) & "/01/" & Year(Date) 'Start Date
    fWS.Range("AH4").Value = "<=" & Date 'End Date
End If

Just to explain, dVer is a boolean that returns true if a valid date has been entered in my userform, or false if not. With this code and today's date the code will return >=3/01/2016 for cell AG4 and <=3/22/2016 for cell AH4 which for the purpose of my advanced filter is exactly what I needed. Now if no date is entered by a user they will only see the current month to date. Hope this helps someone!