vba - 在所有工作表上应用 VBA 代码

Question

我的 VBA 代码按特定列(即“AT”)对工作表进行排序。我想要的是让这个 VBA 同时应用于工作簿的所有工作表。我的原始工作代码如下所示:

Dim sort As String
Dim area As String
area = "A4:FJ4100"
sort = "AT"
ActiveSheet.Range(area).Sort _
Key1:=Range(sort & "1"), Order1:=xlDescending, _
Header:=xlGuess, MatchCase:=False, _
Orientation:=xlTopToBottom
Application.ScreenUpdating = False 

我尝试添加

dim sh as Worksheet 
For each sh in ActiveWorkbook.Worksheets 
sh.activate 
"my code"
Next

但这不起作用。很高兴有任何帮助!

Answer 1

如果将代码放入模块中，这是一种使代码正常工作的方法。它避免了 Select和 Active ，因此它使它更加健壮:

Public Sub Main()

    Dim sh As Worksheet

    For Each sh In ThisWorkbook.Worksheets
        SortSomething sh
    Next sh

End Sub

Sub SortSomething(sh As Worksheet)

    Dim sort As String
    Dim area As String
    area = "A4:FJ4100"
    sort = "AT"
    sh.Range(area).sort _
    Key1:=sh.Range(sort & "1"), Order1:=xlDescending, _
        Header:=xlGuess, MatchCase:=False, _
        Orientation:=xlTopToBottom
        Application.ScreenUpdating = False

End Sub