使 VBA 代码影响特定范围的页面

Question

我有这个非常简单的代码

    Sub SortByTime()
'
' SortByTime Macro
' Sort by Time
'
' Keyboard Shortcut: Ctrl+Shift+M
'
    Range("C4:J43").Select
    ActiveWindow.SmallScroll Down:=-36
    ActiveWorkbook.Worksheets("Jan_1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Jan_1").Sort.SortFields.Add2 Key:=Range("G4:G43") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Jan_1").Sort
        .SetRange Range("C3:J43")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

我想要做的是让这段代码影响 365 张工作表（不是所有的工作簿工作表），我每个月有 30 张工作表，我不知道如何编辑这段代码，我试图像这样添加工作表，使用 ActiveWorkbook.Worksheets（“Jan_1”+“Jan_2”+“Jan_3”+“Jan_4”+“Jan_5”+.... 等）。排序

但它没有起作用

提前致谢

Answer 1

这段代码可以达到这个目的：

Sub SortByTime()

    Dim wrkSht As Worksheet
    Dim LastRow As Long
    Dim RangeToSort As Range
    
    For Each wrkSht In ThisWorkbook.Worksheets
        With wrkSht
            LastRow = .Cells(.Rows.Count, 7).End(xlUp).Row 'Find last row in column G. The 7 in .Cells(..) is column G.
            Set RangeToSort = .Range(.Cells(3, 3), .Cells(LastRow, 10))
            
            Select Case .Name
                Case "Sheet1", "Sheet2", "Sheet4" 'Add the sheets you want to sort here.
                    .Sort.SortFields.Clear
                    .Sort.SortFields.Add2 Key:=.Range(.Cells(4, 7), .Cells(LastRow, 7)), _
                                          SortOn:=xlSortOnValues, _
                                          Order:=xlAscending, _
                                          DataOption:=xlSortNormal
                    With .Sort
                        .SetRange RangeToSort
                        .Header = xlYes
                        .MatchCase = False
                        .Orientation = xlTopToBottom
                        '.SortMethod = xlPinYin  Not needed used for sorting Chinese characters.
                        .Apply
                    End With
                    
                'Case else isn't needed - just there to show nothing happens
                'with other sheets.  If you're sorting a lot of sheets then might be better
                'to put sorting code in here and list the sheets you don't want sorting above.
                Case Else
                    'Do nothing
            End Select
            
        End With
    Next wrkSht

End Sub

进一步阅读：
With...End With 语句
本工作簿
选择案例陈述