VBA to filter a specific month without changing the filter code

[josephmary]

Hi,

I am currently using ThisMonth Filter code to filter the current report month. I send my reports daily adding the previous day data. I need a code where (if possible) filters the month based on the previous day data. Reason why I need this is because if the previous day falls on the previous month but I am reporting in on the new Month (eg. data is as of Nov 30 but I am sent it Dec 1). Because my current code is ThisMonth, it filters december (which has no data). It's a hassle changing it everytime to LastMonth. Hoping for an answer. Thank you

 

[Alex Blakenburg]

See if this gives you any ideas:
(filter in code is on field 4)

Sub testFilter()
    Dim fltrMth As Long
    
    fltrMth = xlFilterThisMonth
    If Day(Date) = 1 Then fltrMth = xlFilterLastMonth
    
    ActiveSheet.Range("$A$1:$E$17").AutoFilter Field:=4, _
                Operator:=xlFilterDynamic, _
                Criteria1:=fltrMth
                
End Sub

 

[josephmary]

will test and give you updates. thank you

 

[josephmary]

tried to work around on it but the filter is empty. will try to combine it with the ones i have and work on the Day function. thank you

 

[Alex Blakenburg]

If you find that you need more specific help show us your code and some sample data and we can take a look.

 

[josephmary]

below is my current code for filtering current month.

Sub output()
Sheets("WB").Select
'Filtering current month data
Dim i As Integer, rngData As Range
Set rngData = Range("A1").CurrentRegion
i = Application.WorksheetFunction.Match("Month", Range("A1:AN1"), 0)

rngData.AutoFilter Field:=i, Criteria1:=xlFilterThisMonth, Operator:=xlFilterDynamic

End Sub

Here's the one I tried now using your code and adding some conditions:

Sub testFilter()
Dim fltrMth As Long
Dim theDate As Date
theDate = Now()
fltrMth = xlFilterThisMonth
If Day(#12/1/2023#) = theDate Then fltrMth = xlFilterLastMonth

ActiveSheet.Range("A1:AN1").AutoFilter Field:=31, _
Operator:=xlFilterDynamic, _
Criteria1:=fltrMth
End Sub

It's still filtering the current month instead of last month. Am i missing something here?

 

[Alex Blakenburg]

It's still filtering the current month instead of last month. Am i missing something here?

Yes you are:
• I used Date instead of Now since you don't need the time element ie date is just the date portion, now is date and time
(this will not stop if from working though)
• Day(#12/1/2023#) - Not sure why you want to hard code a date, this is going to return the date portion of #12/1/2023# and is always going to be 1.
Which is why I just used 1 here.
• If Day(.#12/1/2023#) = theDate
This is comparing the Day of #12/1/2023# to the full date in theDate (which is turn is now)
ie 1 = 12/7/2023 7:54:30 AM which is never going to be TRUE

What criteria do you want to use to trigger using the previous month ?

To force it to use the previous month only when today's date is the 1st of the month the mininum change would be:-

If Day(#12/1/2023#) = Day(theDate) Then fltrMth = xlFilterLastMonth

My preference would be to have a cell in the spreadsheet called say Date Run, and read that into the code.
You could have that set to being =TODAY() in normal use.
But it means that if you want to test the code you can type in a date to use ie you now want to force in 1 Dec for testing and say 1 Dec fell on a weekend and you now want to run it on the 2nd or 3rd for the prior month you don't have to change the code to do so, you can just put in 1 Dec knowing that will trigger it to run the previous month.

 

[josephmary]

Still the same. I think it's the Criteria that has an issue. When is changed it to last month, it filters to last month. But what I want is for the criteria to read the If Then statement.

 

[josephmary]

will try again and give you updates

 

[Alex Blakenburg]

How do you want to tell the code you want it to pull the previous month data ?