I'm new to VBA.

Currently, we are conducting physical fitness tests for various items and entering the next measurement time in the FS cell.

From here, I would like to autofilter only those that have a specific month (for example, January) and copy them to another sheet. I took a look and wrote the code as below.

Sheets ("Sheet3"). Select
Dim Filter_Month
Dim Criteria_Month
Dim Target As Range, LastCell As Range
Set Target = Sheets ("Basic evaluation next measurement"). Cells (Rows.Count, 1) .End (xlUp) .Offset (1, 0)

 Filter_Month = InputBox ("Enter the month I want to extract")
    Select Case Filter_Month
        Case "1"
            Criteria_Month = xlFilterAllDatesInPeriodJanuary
        Case "2"
            Criteria_Month = xlFilterAllDatesInPeriodFebruray
        Case "3"
            Criteria_Month = xlFilterAllDatesInPeriodMarch
        Case "4"
            Criteria_Month = xlFilterAllDatesInPeriodApril
        Case "5"
            Criteria_Month = xlFilterAllDatesInPeriodMay
        Case 6
            Criteria_Month = xlFilterAllDatesInPeriodJune
        Case "7"
            Criteria_Month = xlFilterAllDatesInPeriodJuly
        Case "8"
            Criteria_Month = xlFilterAllDatesInPeriodAugust
        Case "9"
            Criteria_Month = xlFilterAllDatesInPeriodSeptember
        Case "10"
            Criteria_Month = xlFilterAllDatesInPeriodOctober
        Case "11"
            Criteria_Month = xlFilterAllDatesInPeriodNovember
        Case "12"
            Criteria_Month = xlFilterAllDatesInPeriodDecember
    End Select
    Range ("A1"). AutoFilter field: = 175, Criteria1: = Criteria_Month, Operator: = xlFilterDynamic
Range ("A1"). CurrentRegion.SpecialCells (xlVisible) .Copy Target
ActiveSheet.AutoFilterMode = False
End Sub

When I run it, it gets stuck at the Range method.

I'm stuck, so I'd like to ask a professor.

  • Answer # 1

    Range.Autofilter doesn't seem to be supported on Mac.

    Range.AutoFilter method (Excel)

    Excel for Mac does not support this method.


    Similar methods for Selection and ListObject are supported.

    Because there is

    Range ("A1"). CurrentRegion.Select
    Selection.AutoFilter field: = 175, Criteria1: = Criteria_Month, Operator: = xlFilterDynamic

    Why don't you try it like this?
    I'm sorry if I make a mistake because there is no environment that can be tested immediately.

  • Answer # 2

    On our side, as long as you specify 1 to 12, no error will occur.
    If you specify a value other than 1 to 12, the same error will occur. .. ..
    The difference from the questioner's code is that the FS column (Fields: = 175) is set to the A column (Fields: = 1) and the date is set in the A column.

    Does Criteria_Month have the correct value in Range in step execution?

Related articles