问对1个工作簿中的工作表进行排序
EN

Stack Overflow用户

提问于 2018-06-14 10:13:21

回答 3查看 39关注 0票数 0

我在网上搜索了一个可以帮助我对工作簿中的工作表进行排序的宏，并对其进行了一些修改(添加了排除工作表)

Sub SortDataWorksheets()

Dim wsh As Worksheet
For Each wsh In ThisWorkbook.Sheets
    If wsh.Name <> "Dashboard" And wsh.Name <> "rawdata" And wsh.Name <> "template" And wsh.Name <> "macros instructions" And wsh.Name <> "Sheet1" _
    And wsh.Name <> "Sheet2" And wsh.Name <> "inputlist" And wsh.Name <> "ProductList" And wsh.Name <> "NA" Then

    'sort columns A to AL based on data in column B
    wsh.Columns("A:AL").Sort key1:=Range("B3"), order1:=xlAscending, Header:=xlYes
    End If
Next

End Sub

但是，这并不起作用，因为excel将抛出

Run Time error '1004' :
The sort reference is not valid. Make sure that it's within the data you want to sort...

我的数据从第3行开始，前2行是标题。如何排除要排序的前2行？

excel

vba

回答 3

Stack Overflow用户

发布于 2018-06-14 10:29:36

更改自：

wsh.Columns("A:AL").Sort key1:=Range("B3"), order1:=xlAscending, Header:=xlYes

至：

wsh.Columns("A:AL").Sort key1:=wsh.Range("B3"), order1:=xlAscending, Header:=xlYes

因为如果不引用父工作表，VBA会将ActiveSheet或代码所在的工作表作为父工作表。在您的情况下，两者都会返回错误。

票数 1

Stack Overflow用户

发布于 2018-06-14 10:31:07

这对我来说很有效：

Sub SortDataWorksheets()

    Dim wsh As Worksheet
    Dim LastRow As Long

    For Each wsh In ThisWorkbook.Sheets
        With wsh
            If .Name <> "Dashboard" And .Name <> "rawdata" And .Name <> "template" And _
                .Name <> "macros instructions" And .Name <> "Sheet1" _
                And .Name <> "Sheet2" And .Name <> "inputlist" And _
                .Name <> "ProductList" And .Name <> "NA" Then

                LastRow = .Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row

                'sort columns A to AL based on data in column B
                .Range("A2:AL" & LastRow).Sort key1:=.Range("B3"), order1:=xlAscending, Header:=xlYes
            End If
        End With
    Next

End Sub

请注意，这里使用的是特定范围而不是列。

票数 0

Stack Overflow用户

发布于 2018-06-14 10:36:45

我发现在忽略工作表时，读取一个Select Case比读取多个IF..AND..THEN更容易。

下面的代码将调整为B列中包含数据的行数。

我仍然不确定哪种排序方法是首选的-单行，或者宏记录器返回什么(类似于下面)。

Public Sub SortDataWorksheets()

    Dim wsh As Worksheet
    Dim lLastRow As Long

    For Each wsh In ThisWorkbook.Worksheets
        Select Case wsh.Name
            Case "Dashboard", "rawdata", "template", "macros instructions", _
                 "Sheet1a", "Sheet2a", "inputlist", "ProductList", "NA"

                 'Do nothing

            Case Else

                lLastRow = wsh.Cells(wsh.Rows.Count, 2).End(xlUp).Row

                With wsh.Sort

                    With .SortFields
                        .Clear
                        .Add Key:=Range("B5:B" & lLastRow), _
                             SortOn:=xlSortOnValues, _
                             Order:=xlAscending, _
                             DataOption:=xlSortNormal
                    End With

                    .SetRange Range("A5:C" & lLastRow)
                    .Header = xlNo
                    .MatchCase = False
                    .Orientation = xlTopToBottom
                    '.SortMethod = xlPinYin 'Only need if sorting Chinese characters.
                    .Apply

                End With

        End Select
    Next wsh

End Sub