对具有相同范围的多个表进行排序

Trying to sort multiple sheets, used an array and unfortunately getting "Cannot set array" error. Not sure what is being done wrong here. Here is my code:

尝试对多个表进行排序，使用一个数组，不幸地得到“不能设置数组”错误。不知道这里做错了什么。这是我的代码:

Sub Macro1()

Dim ws() As Variant
Dim wb As Workbook
Set wb = ThisWorkbook
Set ws() = wb.Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
'
' Macro1 Macro
'

'
ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=Range("A1"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ws.Sort
    .SetRange Range("A1:B6")
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
End Sub

2 个解决方案

#1

The recorded Sort syntax is way more than you actually require.

所记录的排序语法远远超出了您实际需要的。

sub macro2()
    dim w as long, lr as long, wss as variant

    wss = Array("Sheet1", "Sheet2", "Sheet3")

    for w = lbound(wss) to ubound(wss)
        with thisworkbook.worksheets(wss(w))
            lr = application.max(.cells(.rows.count, "a").end(xlup).row, _
                                 .cells(.rows.count, "b").end(xlup).row)
            with .range(.cells(1, "a"), .cells(lr, "b"))
                .Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
                            Orientation:=xlTopToBottom, Header:=xlNo
            end with
        end with
    next w
end sub

#2

You are referring to an array where you should be referring to a sheet. You declared ws as Variant & this variable does not have properties such as .Sort

你指的是一个数组，你应该指的是一个表格。您将ws声明为变量&该变量没有.Sort之类的属性

Notice that the array can be used as a shortcut to the sheet name, not as a shortcut to the .Sheets("") object.

注意，数组可以用作表名的快捷方式，而不是. sheets(“”)对象的快捷方式。

Sub Macro1()

Dim ws() As Variant
Dim wb As Workbook
Set wb = ThisWorkbook
ws() = Array("Sheet1", "Sheet2", "Sheet3")
Dim i as Integer

For i = 0 To 2
    wb.Sheets(ws(i)).Sort.SortFields.Clear
    wb.Sheets(ws(i)).Sort.SortFields.Add Key:=Range("A1"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With wb.Sheets(ws(i)).Sort
    .SetRange Range("A1:B6")
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
Next i

End Sub

#1