How can I sort worksheets in ascending/descending order based on the value of the same cell in each sheet?

[Davao_Danny]

Hi everyone.

I need two similar macros: one each for sorting worksheets in ascending/descending order based on the value of the same cell in each worksheet.

The first and last worksheets (named as "cover page" and "summary") need to remain where they are, but every worksheet in between these needs to be sorted based on the value of cell A5 in each.

Any help will be highly appreciated.

Thanks in advance.
Danny <!-- / message -->

 

[Michaels]

Hi !

Hope this helps.

http://www.cpearson.com/excel/sortws.aspx

 

[Davao_Danny]

Thanks for the info, Shawn. I'll let you know if I get stuck.

Danny

 

[Jon von der Heyden]

It could probably be a whole lot easier if the value in A5 in each sheet is guaranteed to be numeric. This method however can also sort based on textual values in A5, because it uses the normal sort method.

Sub CustomSortSheets()
'// sorts sheets in an order according to values housed in a A5 in each sheet
    Dim arrVALUES()
    Dim lngSHEETSCOUNT As Long
    Dim wks As Worksheet
    Dim rngCELL As Range
    
    lngSHEETSCOUNT = ActiveWorkbook.Sheets.Count
    ReDim arrVALUES(1 To lngSHEETSCOUNT, 1 To 2)
    
    For Each wks In Worksheets
        arrVALUES(wks.Index, 1) = wks.Range("A5").Value
        arrVALUES(wks.Index, 2) = wks.Name
    Next wks
    
    With Sheets.Add
        With .Range("A1:B" & lngSHEETSCOUNT)
            .Value = arrVALUES
            .Sort key1:=.Cells(1)
        End With
        On Error Resume Next
            For Each rngCELL In .Range("B1:B" & lngSHEETSCOUNT)
                Sheets(rngCELL.Text).Move after:=Sheets(rngCELL.Offset(-1).Text)
            Next rngCELL
        On Error GoTo 0
        Application.DisplayAlerts = False
            .Delete
        Application.DisplayAlerts = True
    End With
End Sub

 

[Davao_Danny]

Thank you very much for your help, Jon.

Danny

 

[Comisar]

Jon,

The code you posted works perfectly. Is it possible to also have the code ignore specific sheets by adding in an if statement like this:

For Each wks In Worksheets
    
       If  wks.Name <> "Overview" And wks.Name <> "List" And wks.Name  <> "Template" And wks.Name <> "First" Then
Else
     
        arrVALUES(wks.Index, 1) = wks.Range("E13").Value
        arrVALUES(wks.Index, 2) = wks.Name
        End If
     Next wks

I tried this, but the code no longer works.