Excel VBA Sort Multiple Tabs (not all tabs)

[jrlop]

Hi guys,
I have an excel file with many tabs. For the most part my data goes from column A5 - H150. I am wanting to sort some tabs (smallest to largest) by column C, some tabs by column E and other tabs by column H. I have the tabs grouped those that need to be sorted by column C, followed by column E and then H sort tabs. I know very little about VBA but this one is pretty advanced for me. I am hoping to be able to create three different Macros (one that sorts column C, column E and Column H) and run each separately. I really do not want to have to sort this manually and I have to create this same report for a total of 4 teams. I greatly appreciate your help. Jorge

 

[Trevor G]

Here is an example on sorting Column H for 3 sheets you will need to adapt for the others.

Sub SortMultipleSheets()
Dim ws As Worksheet

For Each ws In Sheets 'This statement starts the loop. Example for 3 sheets you would need to add more
If ws.Name = "Clinical KPI OBHG De" Or ws.Name = "Clinical KPI OBHG Del %" Or ws.Name = "Clinical KPI OSA Del" Then
'Sort the data using column H
ws.Activate
Range("H6").CurrentRegion.Select
With Selection
    .Sort Key1:=Range("H6"), Order1:=xlAscending, Header:=xlYes
    Range("A1").Select
    End With
End If
Next ws
End Sub

 

[jrlop]

Just put the column letter & then you could use

Sub jrlop()
   Dim Rng As Range, Cl As Range
  
   With Sheets("KutoolsforExcel")
      Set Rng = .Range("A2", .Range("B" & Rows.Count).End(xlUp))
   End With
   For Each Cl In Rng
      If Cl.Offset(, 1) <> "" Then
         With Sheets(Cl.Value)
            .Range("A5:" & Cl.Offset(, 1) & "200").Sort key1:=.Range(Cl.Offset(, 1) & "5"), order1:=xlAscending, Header:=xlYes
         End With
      End If
   Next Cl
End Sub

The sheet names in col A must be an exact match for the actual name of the sheet.

I am getting an error on this line
.Range("A5:" & Cl.Offset(, 1) & "200").Sort key1:=.Range(Cl.Offset(, 1) & "5"), order1:=xlAscending, Header:=xlYes

Here is what I have so far. I changed the reference sheet to "Index" Let me know if I missed something. Thank you!
Sub QOR_Sorting()
Dim Rng As Range, Cl As Range

With Sheets("Index")
Set Rng = .Range("A2", .Range("B" & Rows.Count).End(xlUp))
End With
For Each Cl In Rng
If Cl.Offset(, 1) <> "" Then
With Sheets(Cl.Value)
.Range("A5:" & Cl.Offset(, 1) & "200").Sort key1:=.Range(Cl.Offset(, 1) & "5"), order1:=xlAscending, Header:=xlYes
End With
End If
Next Cl
End Sub

 

[jrlop]

I am getting an error on this line
.Range("A5:" & Cl.Offset(, 1) & "200").Sort key1:=.Range(Cl.Offset(, 1) & "5"), order1:=xlAscending, Header:=xlYes

Here is what I have so far. I changed the reference sheet to "Index" Let me know if I missed something. Thank you!
Sub QOR_Sorting()
Dim Rng As Range, Cl As Range

With Sheets("Index")
Set Rng = .Range("A2", .Range("B" & Rows.Count).End(xlUp))
End With
For Each Cl In Rng
If Cl.Offset(, 1) <> "" Then
With Sheets(Cl.Value)
.Range("A5:" & Cl.Offset(, 1) & "200").Sort key1:=.Range(Cl.Offset(, 1) & "5"), order1:=xlAscending, Header:=xlYes
End With
End If
Next Cl
End Sub

Fluff,
Man you are a true genius!! It was a mistake I made. I did not see that you said to enter the letter in the sort column and I had entered the number instead of the column letter. I greatly appreciate the amount of time you just saved me!!! Thank you so much!

 

[Fluff]

Glad we could help & thanks for the feedback.