Desire/need to sort all sheets in a work book by a specific cell, across all sheets.

Hi Schwarz, Kevin

Are you using Excel on Window or Mac?

Excel can sort data within a worksheet, but it does not provide a built-in command to reorder worksheet tabs based on cell values. A VBA macro can automate this process.

First, save a backup copy of the workbook. Then use the following macro:

Sub SortSheetsByCellValue()
    Const SORT_CELL As String = "B2"   'Change to the required cell
    Const ASCENDING As Boolean = True  'False = descending
    Dim ws As Worksheet
    Dim sheetNames() As String
    Dim sortValues() As Variant
    Dim i As Long, j As Long
    Dim tempName As String
    Dim tempValue As Variant
    Dim shouldSwap As Boolean
    Application.ScreenUpdating = False
    Application.Calculate
    ReDim sheetNames(1 To ThisWorkbook.Worksheets.Count)
    ReDim sortValues(1 To ThisWorkbook.Worksheets.Count)
    i = 1
    For Each ws In ThisWorkbook.Worksheets
        sheetNames(i) = ws.Name
        sortValues(i) = ws.Range(SORT_CELL).Value
        i = i + 1
    Next ws
    For i = 1 To UBound(sheetNames) - 1
        For j = i + 1 To UBound(sheetNames)
            shouldSwap = False
            'Keep blank cells at the end
            If Len(sortValues(i) & "") = 0 Then
                If Len(sortValues(j) & "") > 0 Then shouldSwap = True
            ElseIf Len(sortValues(j) & "") > 0 Then
                If IsNumeric(sortValues(i)) And IsNumeric(sortValues(j)) Then
                    If ASCENDING Then
                        shouldSwap = CDbl(sortValues(i)) > CDbl(sortValues(j))
                    Else
                        shouldSwap = CDbl(sortValues(i)) < CDbl(sortValues(j))
                    End If
                Else
                    If ASCENDING Then
                        shouldSwap = StrComp(CStr(sortValues(i)), _
                                             CStr(sortValues(j)), _
                                             vbTextCompare) > 0
                    Else
                        shouldSwap = StrComp(CStr(sortValues(i)), _
                                             CStr(sortValues(j)), _
                                             vbTextCompare) < 0
                    End If
                End If
            End If
            If shouldSwap Then
                tempName = sheetNames(i)
                sheetNames(i) = sheetNames(j)
                sheetNames(j) = tempName
                tempValue = sortValues(i)
                sortValues(i) = sortValues(j)
                sortValues(j) = tempValue
            End If
        Next j
    Next i
    For i = 1 To UBound(sheetNames)
        ThisWorkbook.Worksheets(sheetNames(i)).Move _
            After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    Next i
    Application.ScreenUpdating = True
End Sub

Before proceeding, save a backup copy of the workbook so that you can return to the original worksheet order if necessary.

1. Open the workbook in the Excel desktop application. VBA macros cannot be created or run in Excel for the web.
2. Select File > Save As, then save the workbook as an Excel Macro-Enabled Workbook (*.xlsm). A regular .xlsx file cannot retain VBA macro code.
3. Press Alt+F11 to open the Visual Basic Editor.
4. In the Visual Basic Editor, look for the Project Explorer pane on the left. If it is not visible, select View > Project Explorer.
5. In Project Explorer, locate the project whose name matches your workbook
6. Select that project, then select Insert > Module from the menu. A new blank code window should appear.
7. Copy the complete macro provided above and paste it into the blank module window.
8. Near the beginning of the macro, change "B2" to the address of the cell containing the calculated result on every worksheet. For example, use "F10" if the sorting value is stored in cell F10.
9. Leave ASCENDING = True to arrange the worksheet tabs from the smallest value to the largest. Change it to False if you want the largest value first.
10. Press Ctrl+S to save the workbook, then close the Visual Basic Editor or press Alt+F11 again to return to Excel.
11. In Excel, press Alt+F8 to open the Macro dialog box.
12. Select SortSheetsByCellValue, and then select Run. The macro will read the specified cell from each worksheet and reorder the worksheet tabs according to those values.

If Excel displays a security warning, select Enable Content only if you trust this workbook and the macro code. If the macro does not appear in the list, confirm that the workbook was saved as an .xlsm file and that the code was pasted into a standard Module, rather than into a worksheet or ThisWorkbook.

Run the macro on a copy first, especially if the workbook contains formulas that depend on worksheet positions. Blank values will be placed at the end of the worksheet list.

The macro recalculates the workbook, reads the selected cell from every worksheet, and moves the worksheet tabs into the corresponding order. Blank results are placed at the end. VBA can loop through all worksheets in a workbook, which makes it suitable for this type of automation.

I hope this helps make managing a large number of response sheets much quicker.

Feel free reply back to this post if you need further assistance, I'll be happy to help