Excel VBA Sort Multiple Tabs (not all tabs)

jrlop

New Member
Joined
Jul 27, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jul 17, 2008
Messages
6,707
Office Version
  1. 2016
Platform
  1. Windows
Here is an example on sorting Column H for 3 sheets you will need to adapt for the others.

VBA Code:
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

jrlop

New Member
Joined
Jul 27, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Just put the column letter & then you could use
VBA Code:
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

New Member
Joined
Jul 27, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Forum statistics

Threads
1,141,074
Messages
5,704,147
Members
421,328
Latest member
mippy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top