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

My VBA code sorts a sheet by a certain column, i.e. "AT". What I like to have is for this VBA to apply on all the sheets of the workbook at the same time. My original working code looks like this:

我的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

I tried adding

我尝试添加

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

but this does not work. Happy for any help!

但这不起作用。很高兴为您提供帮助！

2 个解决方案

#1

This is a way to make your code working, if you put it in a module. It avoids Select and Active, thus it makes it a bit more robust:

如果将代码放在模块中，这是一种使代码正常工作的方法。它避免了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

#2

In your "For Each" loop, after the sheet activate, you need to apply the range on loop 'ws' var. Try this, it's work fine for me :

在“For Each”循环中，激活工作表后，您需要在循环'ws'var上应用范围。试试这个，它对我来说很好用：

Sub test()

Dim ws As Worksheet
For Each ws In Worksheets

    Dim sort As String
    Dim area As String

    area = "A4:FJ4100"
    sort = "AT"
    ws.Activate 'First activate the sheet
    ws.Range(area).sort _
    Key1:=Range(sort & "1"), Order1:=xlDescending, _
    Header:=xlGuess, MatchCase:=False, _
    Orientation:=xlTopToBottom
    Application.ScreenUpdating = False

Next

End Sub

#1