CODEWITHSUNDEEP
excelvba
Mirsardor Tulaganov
Mirsardor Tulaganov

Reputation: 17

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

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

I wrote VBA code that:

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

To reduce the code, I tried to wrap it into the For Each loop statement. It still works, 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

I searched the internet, including similar questions here, but it does not work for me.

Upvotes: 0

Views: 174

Answers (1)

Storax
Storax

Reputation: 12167

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.

or

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

Upvotes: 2

Related Questions