Sort worksheets by cell content
 

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.

Sort worksheets by cell content
 

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 _
befo=Worksheets(wCtr - 1)
SwappedSheets = True
End If
Next wCtr
If SwappedSheets = False Then
Exit Do
End If
Loop

End Sub




R3dD0g wrote:

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

Sort worksheets by cell content
 

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 befo=Sheets(1)
For i = 2 To Sheets.Count
Sheets(Sheets("helper").Cells(i, 1).Value).Move after:=Sheets(i - 1)
Next
End Sub
--
Gary''s Student - gsnu200805


"R3dD0g" wrote:

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.

Sort worksheets by cell content
 

That worked.

Thank you.

"Gary''s Student" wrote:

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 befo=Sheets(1)
For i = 2 To Sheets.Count
Sheets(Sheets("helper").Cells(i, 1).Value).Move after:=Sheets(i - 1)
Next
End Sub
--
Gary''s Student - gsnu200805


"R3dD0g" wrote:

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.

Sort worksheets by cell content
 

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.

"Dave Peterson" wrote:

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 _
befo=Worksheets(wCtr - 1)
SwappedSheets = True
End If
Next wCtr
If SwappedSheets = False Then
Exit Do
End If
Loop

End Sub




R3dD0g wrote:

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

Sort worksheets by cell content
 

I agree. Some people would also like to be able to group worksheets (like
folders).
--
Gary''s Student - gsnu200805


"R3dD0g" wrote:

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.

"Dave Peterson" wrote:

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 _
befo=Worksheets(wCtr - 1)
SwappedSheets = True
End If
Next wCtr
If SwappedSheets = False Then
Exit Do
End If
Loop

End Sub




R3dD0g wrote:

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