Glitch5618
Board Regular
- Joined
- Nov 6, 2015
- Messages
- 105
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:
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:
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.
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:
Rich (BB code):
'Filter for current month
With dWS
.AutoFilterMode = False
With fRng
.AutoFilter Col, Criteria1:=xlFilterThisMonth, Operator:=xlFilterDynamic
On Error Resume Next
End With
End With
Rich (BB code):
'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.