Sorting by two columns across all tabs- VBA

[frustrated_macro]

I have 31 tabs in this report and i need to sort the same 2 columns across all 31 tabs
i have tried a few things and it either sorts nothing, or gives me an error

With ActiveSheet.Sort
.SortFields.Clear
.SortFields.Add Key:=ActiveSheet.Range("I1"), Order:=xlAscending
.SortFields.Add Key:=ActiveSheet.Range("G1"), Order:=xlAscending
.SetRange ActiveSheet.Range("A1:O" & LastRow)
.Header = xlYes
.Apply
End With


right above this, i have this bit of code:

Dim s As Worksheet
Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For Each s In ActiveWorkbook.Sheets
Application.Goto s.Range("A2")
ActiveWindow.FreezePanes = True

Next s

i tried adding the With into its own For loop and it only did the sort on whatever page it was on and did not loop through the rest

For Each s In ActiveWorkbook.Sheets
With ActiveSheet.Sort
.SortFields.Clear
.SortFields.Add Key:=ActiveSheet.Range("I1"), Order:=xlAscending
.SortFields.Add Key:=ActiveSheet.Range("G1"), Order:=xlAscending
.SetRange ActiveSheet.Range("A1:O" & LastRow)
.Header = xlYes
.Apply
End With
Next s

help

 

[thirty_two]

As it stands, the "ActiveSheet" is not changing in each cycle of the "For" loop. A modification which might work is changing out the "ActiveSheet" in the "With" statement to "s". In your example, it could look like this:

Dim s As Worksheet
Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row

For Each s In ActiveWorkbook.Sheets
    With s.Sort
        .SortFields.Clear
        .SortFields.Add Key:=s.Range("I1"), Order:=xlAscending
        .SortFields.Add Key:=s.Range("G1"), Order:=xlAscending
        .SetRange s.Range("A1:O" & LastRow)
        .Header = xlYes
        .Apply
    End With
Next s

 

[frustrated_macro]

As it stands, the "ActiveSheet" is not changing in each cycle of the "For" loop. A modification which might work is changing out the "ActiveSheet" in the "With" statement to "s". In your example, it could look like this:

Dim s As Worksheet
Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row

For Each s In ActiveWorkbook.Sheets
    With s.Sort
        .SortFields.Clear
        .SortFields.Add Key:=s.Range("I1"), Order:=xlAscending
        .SortFields.Add Key:=s.Range("G1"), Order:=xlAscending
        .SetRange s.Range("A1:O" & LastRow)
        .Header = xlYes
        .Apply
    End With
Next s

DUH! thank you so much! that did the trick!

 

[Joe4]

As it stands, the "ActiveSheet" is not changing in each cycle of the "For" loop. A modification which might work is changing out the "ActiveSheet" in the "With" statement to "s". In your example, it could look like this:

Dim s As Worksheet
Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row

For Each s In ActiveWorkbook.Sheets
    With s.Sort
        .SortFields.Clear
        .SortFields.Add Key:=s.Range("I1"), Order:=xlAscending
        .SortFields.Add Key:=s.Range("G1"), Order:=xlAscending
        .SetRange s.Range("A1:O" & LastRow)
        .Header = xlYes
        .Apply
    End With
Next s

I see one issue with that - you are finding the value of "LastRow" from the first sheet only, and using that number for all sheets.
You probably want to find the last row of each sheet separately, i.e.

Dim s As Worksheet
Dim LastRow As Long

For Each s In ActiveWorkbook.Sheets
    LastRow = s.Range("A" & s.Rows.Count).End(xlUp).Row
    With s.Sort
        .SortFields.Clear
        .SortFields.Add Key:=s.Range("I1"), Order:=xlAscending
        .SortFields.Add Key:=s.Range("G1"), Order:=xlAscending
        .SetRange s.Range("A1:O" & LastRow)
        .Header = xlYes
        .Apply
    End With
Next s

 

[thirty_two]

I see one issue with that - you are finding the value of "LastRow" from the first sheet only, and using that number for all sheets.
You probably want to find the last row of each sheet separately, i.e.

Dim s As Worksheet
Dim LastRow As Long

For Each s In ActiveWorkbook.Sheets
    LastRow = s.Range("A" & s.Rows.Count).End(xlUp).Row
    With s.Sort
        .SortFields.Clear
        .SortFields.Add Key:=s.Range("I1"), Order:=xlAscending
        .SortFields.Add Key:=s.Range("G1"), Order:=xlAscending
        .SetRange s.Range("A1:O" & LastRow)
        .Header = xlYes
        .Apply
    End With
Next s

Yep, slight thing that I overlooked, thank you for finding such.

 

[Joe4]

No problem! Just a small tweak to your code.

 

[frustrated_macro]

in a similar report i have this code, and then it repeats a ton of times, just changing the sheet name.
how can i also make this into a For loop so if we have to add a new sheet, i dont have to add another block

Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row

Sheets("AS").Select
With ActiveSheet.Sort
.SortFields.Clear
.SortFields.Add Key:=ActiveSheet.Range("C4"), Order:=xlAscending
.SortFields.Add Key:=ActiveSheet.Range("B4"), Order:=xlAscending
.SetRange ActiveSheet.Range("A4:Q" & LastRow)
.Header = xlYes
.Apply
End With

On Error Resume Next
ActiveSheet.Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0

Range("A4:Q" & LastRow).RemoveDuplicates Columns:=Array(6), Header:=xlYes
Cell("A2").Clear

 

[Joe4]

You should be able to use the exact same methodology that thirty_two posted and showed you, looping through the sheets.
I recommend you look at that closely, and try to apply it to your code yourself- that is how you will best learn how to do it yourself (instead of always relying on others to do it for you).

If you cannot get it to work out, please post what you tried and we can help you fix it up.

 

[frustrated_macro]

So i need it to skip the first two tabs, and then do the steps above. I moved the blank cell to the top because it seemed to not be working at the bottom, which is fine
but now, 1- its not looping, its just doing it to whatever tab im on


Dim LR As Long
Dim WSCount As Integer, ws As Integer
WSCount = ActiveWorkbook.Worksheets.Count

For ws = 3 To WSCount

LR = Range("A" & Rows.Count).End(xlUp).Row

On Error Resume Next
ActiveSheet.Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0

With ActiveSheet.Sort
.SortFields.Clear
.SortFields.Add Key:=ActiveSheet.Range("C4"), Order:=xlAscending
.SortFields.Add Key:=ActiveSheet.Range("B4"), Order:=xlAscending
.SetRange ActiveSheet.Range("A4:Q" & LR)
.Header = xlYes
.Apply
End With

Range("A4:Q" & LR).RemoveDuplicates Columns:=Array(6), Header:=xlYes

Next ws

 

[Joe4]

That is because you are NEVER moving off the active sheet!
If you look at the code that you accepted as the solution, there is no reference to the ActiveSheet in that code!

Note that this line:

For ws = 3 To WSCount

loops through the counter, but never moves to any other sheets. You are just iterating through a list of numbers.

One simple way to do what you want with the code you currently have is to select each sheet, so it then becomes the active sheet.
Then your code would work as-is. So you would just need to add the line in red shown below:

For ws = 3 To WSCount
Sheets(ws).Select

Note that it isn't as efficient as the other method shown previously, as it often isn't necessary to select objects in order to work with them (and selects actually slow your code down), but it should work nonetheless.