CODEWITHSUNDEEP
vbaexcel
os23
os23

Reputation: 145

Applying VBA Code on all sheets

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:

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!

Upvotes: 2

Views: 2273

Answers (2)

JC Guidicelli
JC Guidicelli

Reputation: 1316

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 :

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

Upvotes: 0

Vityata
Vityata

Reputation: 43575

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:

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

Upvotes: 1

Related Questions