excel - 除了为任何工作表运行的当前代码之外，用于在 excel 中排序的 VBA

Question

我正在尝试从其他命令列表中添加此 VBA，但出现错误。请协助正确的排序语法。谢谢你。

错误信息是:运行时错误'1004' 应用程序定义或对象定义错误。

    Sub filter()
Dim N As Long
Dim wsName As String


For N = 1 To ThisWorkbook.Sheets.Count
    wsName = ThisWorkbook.Worksheets(N).Name

    If Len(wsName) = 3 Then

    Sheets(wsName).Range("$A$1:$XFC$1104").AutoFilter Field:=12, Criteria1:=">=365" _
        , Operator:=xlAnd
    Sheets(wsName).Range("$A$1:$XFC$1104").AutoFilter Field:=17, Criteria1:=">100" _
        , Operator:=xlAnd
    Sheets(wsName).Range("$A$1:$XFC$7606").AutoFilter Field:=20, SortOn:=xlSortOnValues, Order:=xlDescending _
        , Operator:=xlAnd
    With ActiveWorkbook.Worksheets("i_ULO").AutoFilter.sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With


    Else
end if 
next n
end sub

在下面添加此代码后。出现了新的错误。

Dim N As Long

将 wsName 变暗为字符串

对于 N = 1 到 ThisWorkbook.Sheets.Count
wsName = ThisWorkbook.Worksheets(N).Name

If Len(wsName) = 3 Then
With .Cells(1, "A").CurrentRegion
     .Cells.sort Key1:=.Columns(20), Order1:=xlDescending, _
                 Orientation:=xlTopToBottom, Header:=xlYes
End With
End With

Compile Error

reference

Answer 1

试试这个:

Sub filter()

    Dim N As Long
    Dim wsName As String

    For N = 1 To ThisWorkbook.Sheets.Count
        wsName = ThisWorkbook.Worksheets(N).Name

        If Len(wsName) = 3 Then

            Sheets(wsName).Range("$A$1:$XFC$1104").AutoFilter Field:=12, Criteria1:=">=365" _
                , Operator:=xlAnd
            Sheets(wsName).Range("$A$1:$XFC$1104").AutoFilter Field:=17, Criteria1:=">100" _
                , Operator:=xlAnd
            Sheets(wsName).AutoFilter.Sort.SortFields.Add Key:=Range("T1:T7606"), SortOn:=xlSortOnValues, Order:=xlDescending

            With ActiveWorkbook.Worksheets("i_ULO").AutoFilter.Sort
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With

        End If

    Next N

End Sub