Sort worksheets by cell content

[R3dD0g]

I'm not much of an Excel person, I'm more comfortable in SQL & VB.Net.

I have a workbook with sheets of various scores, and an overal score
averaged & summed into a single cell. I would like to arrange the worksheets
in descending order based upon the contents of cell G13.

I've looked at Pearson's sorting based on the sheet name but I don't know
how to extend it to sorting on a single cell. Any ideas?

Thanks for your time.

 

[Dave Peterson]

This doesn't do as much as Chip's code does, but maybe it'll be sufficient for
you--or at least get you started:

Option Explicit
Sub testme()
Dim myAddr As String
Dim wCtr As Long
Dim SwappedSheets As Boolean

myAddr = "A1"

Do
SwappedSheets = False
For wCtr = 2 To ActiveWorkbook.Worksheets.Count
If Worksheets(wCtr - 1).Range(myAddr).Value _
< Worksheets(wCtr).Range(myAddr).Value Then
Worksheets(wCtr).Move _
before:=Worksheets(wCtr - 1)
SwappedSheets = True
End If
Next wCtr
If SwappedSheets = False Then
Exit Do
End If
Loop

End Sub

 

[Gary''s Student]

We are going to:

1. use a extra worksheet called helper
2. build a tiny table in the helper sheet with all sheetnames in column A
and the values of G13 (in that sheet) in column B
3. sort the table by column B
4. re-arrange the worksheets in the new order:

Sub sortum()
Sheets("helper").Activate
For i = 1 To Sheets.Count
Cells(i, 1).Value = Sheets(i).Name
Cells(i, 2).Value = Sheets(i).Range("G13").Value
Next

Columns("A:B").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlNo

Sheets(Cells(1, 1).Value).Move before:=Sheets(1)
For i = 2 To Sheets.Count
Sheets(Sheets("helper").Cells(i, 1).Value).Move after:=Sheets(i - 1)
Next
End Sub

 

[R3dD0g]

That worked.

Thank you.

We are going to:

1. use a extra worksheet called helper
2. build a tiny table in the helper sheet with all sheetnames in column A
and the values of G13 (in that sheet) in column B
3. sort the table by column B
4. re-arrange the worksheets in the new order:

Sub sortum()
Sheets("helper").Activate
For i = 1 To Sheets.Count
Cells(i, 1).Value = Sheets(i).Name
Cells(i, 2).Value = Sheets(i).Range("G13").Value
Next

Columns("A:B").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlNo

Sheets(Cells(1, 1).Value).Move before:=Sheets(1)
For i = 2 To Sheets.Count
Sheets(Sheets("helper").Cells(i, 1).Value).Move after:=Sheets(i - 1)
Next
End Sub

 

[R3dD0g]

This works, too.

Thank you.

It seems to me that this would be a common requrement for people who use
Excel a whole lot more than me, and Microsoft should include some builtin
functionality to accomplish it.

 

[Gary''s Student]

I agree. Some people would also like to be able to group worksheets (like
folders).