How can I run specific commands through all Excel worksheets via VBA?-CodePudding

Greetings for everyone!

Introduction.

In an Excel workbook, I have two worksheets with similar structures in both.

To automate some procedures, I wrote a simple VBA code that does the followings:

converts format from the text to the data in a range;
sorts the date in a range from oldest to the newest;
filters in a range by the specific characters (the full name of the head of the department, e.g. J.S.Doe);
makes active and moves the view to the top left corner cell in both worksheets;
goes to the next worksheet and repeats the code, then goes to the previous worksheet.

The code.

Sub SuperiorsOrders()
    
    Application.ScreenUpdating = False
    
    Range("I3", Range("I3").End(xlDown)).TextToColumns FieldInfo:=Array(1, 4)
    Range("A3", "J3").End(xlDown).Sort [I2], xlAscending, Header:=xlYes
    Range("A3", "J3").End(xlDown).AutoFilter Field:=8, Criteria1:="J.S.Doe"
    
    Range("A1").Select
    Application.GoTo ActiveSheet.Range("A1"), Scroll:=True
    
    ActiveSheet.Next.Select
    
    Range("I3", Range("I3").End(xlDown)).TextToColumns FieldInfo:=Array(1, 4)
    Range("A3", "J3").End(xlDown).Sort [I2], xlAscending, Header:=xlYes
    Range("A3", "J3").End(xlDown).AutoFilter Field:=8, Criteria1:="J.S.Doe"
    
    Range("A1").Select
    Application.GoTo ActiveSheet.Range("A1"), Scroll:=True
    
    ActiveSheet.Previous.Select
    
    Application.ScreenUpdating = True

End Sub

The problem.

To reduce the code, I tried to wrap it into the For Each loop statement. It still works well, but only for active worksheet, not for all of them.

Sub SuperiorsOrders()

Application.ScreenUpdating = False

Dim WS As Worksheet

For Each WS In Worksheets
    Range("I3", Range("I3").End(xlDown)).TextToColumns FieldInfo:=Array(1, 4)
    Range("A3", "J3").End(xlDown).Sort [I2], xlAscending, Header:=xlYes
    Range("A3", "J3").End(xlDown).AutoFilter Field:=8, Criteria1:="J.S.Doe"
    Range("A1").Select
    Application.GoTo ActiveSheet.Range("A1"), Scroll:=True
Next WS

Application.ScreenUpdating = True

End Sub

The question.

Could you help me please rewrite the code to make it work correctly?

The remark

I tried the search for the solution on the internet, including similar questions here, but it does not work for me.

CodePudding user response：

You have to add the worksheet reference to the range in the loop otherwise Range always refers to the active sheet

    ws.Range("I3", ws.Range("I3").End(xlDown)).TextToColumns FieldInfo:=Array(1, 4)
   ' add the remaining lines of code starting with ws.

With  ws 
   .Range("I3", .Range("I3").End(xlDown)).TextToColumns FieldInfo:=Array(1, 4)
   ' add the remaing lines of code in the same way
End With

So your code would look like that

Sub SuperiorsOrders()

    Application.ScreenUpdating = False

    Dim WS As Worksheet

    For Each WS In Worksheets
        With WS
            .Range("I3", .Range("I3").End(xlDown)).TextToColumns FieldInfo:=Array(1, 4)
            .Range("A3", "J3").End(xlDown).Sort [I2], xlAscending, Header:=xlYes
            .Range("A3", "J3").End(xlDown).AutoFilter Field:=8, Criteria1:="J.S.Doe"
            ' .Range("A1").Select  That is not necessary
        End With
        'Application.GoTo ActiveSheet.Range("A1"), Scroll:=True  <= What is that good for?
    Next WS

    Application.ScreenUpdating = True

End Sub