AutoFilter / Sort VBA-CodePudding

I have a data set that has new data added to it each month from another spreadsheet. The other spreadsheet. I recorded the macro to do what I want it to do this month, but I don't know how to move the time forward.

As you see in the code below I have criteria "2022" and Criteria "3", which presents a month. How do I create the code to know that the next run should be "2022" and "4" and so on? The month will have a max number of 12 and will eventually turn to "2023" and "1". I appreciate your help!

ActiveSheet.Range("$A$1:$AH$24811").AutoFilter Field:=11, Criteria1:="2022"
ActiveSheet.Range("$A$1:$AH$24811").AutoFilter Field:=12, Criteria1:="3"
ActiveSheet.Range("$A$1:$AH$24811").AutoFilter Field:=5, Criteria1:="=AP", _
    Operator:=xlOr, Criteria2:="=Charges"
ActiveSheet.Range("$A$1:$AH$24811").AutoFilter Field:=1, Criteria1:= _
    "=ST.2", Operator:=xlOr, Criteria2:="=ST.1"
ActiveCell.Offset(6504, 0).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(237, 0).Range("A1").Select

End Sub

CodePudding user response：

This applies the filter using the values from the last row of data.

Option Explicit
Sub FilterData()

    Dim lastrow As Long, yr As Long, pd As Long
    
    With ActiveSheet
        lastrow = .Cells(.Rows.Count, 11).End(xlUp).Row
        yr = .Cells(lastrow, 11).Value2 ' col K
        pd = .Cells(lastrow, 12).Value2 ' col L
        With .Range("A1:AH" & lastrow)
            .AutoFilter Field:=11, Criteria1:=yr
            .AutoFilter Field:=12, Criteria1:=pd
            .AutoFilter Field:=5, Criteria1:="=AP", _
                Operator:=xlOr, Criteria2:="=Charges"
            .AutoFilter Field:=1, Criteria1:="=ST.2", _
                Operator:=xlOr, Criteria2:="=ST.1"
        End With
   End With
End Sub

CodePudding user response：

When you run the macro it will get the current month and year and put them in variables vMonth and vYear.

Sub runMacro()
    Dim vMonth As Long: vMonth = Month(Now())
    Dim vYear As Long: vYear = Year(Now())
    
    ActiveSheet.Range("$A$1:$AH$24811").AutoFilter Field:=11, Criteria1:=vYear
    ActiveSheet.Range("$A$1:$AH$24811").AutoFilter Field:=12, Criteria1:=vMonth
    ' ...
End Sub