Sort all worksheets in ascending order (except sheet1 and sheet2)

[ron2k]

Hi guys,

Scenario: Workbook with several tens of worksheets. The first two are labeled Sheet1 and Sheet2 Resp. All the others are numbers 1:i.

Required: Sort all worksheets 1:i in ascending order and leave Sheet1 and Sheet2 (where they are) at the beginning.

I've searched. A whole lot actually since last night. And everything that I find is for sorting all worksheets in the workbook and doesn't take into consideration that I want the first two to be left untouched.

Thanks

Ron

 

[Ruddles]

If you found a procedure to sort all the sheets, you could have used that and added a couple of commands at the end to move Sheet1 and Sheet2 to the beginning...

... or you could try this:-

Option Explicit
Option Compare Text
 
Public Sub SelectiveSortWorksheets()
 
  Dim wStore() As String
  Dim ws As Worksheet
  Dim aPtr As Integer
  Dim iPtr As Integer
 
  ReDim wStore(0) As String
  aPtr = 0
  For Each ws In ThisWorkbook.Sheets
    If ws.Name = "Sheet1" Or ws.Name = "Sheet2" Then
      ' exclude these sheets
    Else
      aPtr = aPtr + 1
      ReDim Preserve wStore(aPtr) As String
      wStore(aPtr) = ws.Name
    End If
  Next ws
 
  For aPtr = 1 To UBound(wStore) - 1
    For iPtr = aPtr + 1 To UBound(wStore)
      If wStore(aPtr) > wStore(iPtr) Then
        wStore(0) = wStore(iPtr)
        wStore(iPtr) = wStore(aPtr)
        wStore(aPtr) = wStore(0)
      End If
    Next iPtr
  Next aPtr
 
  For aPtr = 1 To UBound(wStore)
    Sheets(wStore(aPtr)).Move After:=Sheets(Sheets.Count)
  Next aPtr
 
End Sub

 

[cjcobra]

Record a macro that selects all sheets but your first two, then sort how you want. Done.

 

[Ruddles]

How does one sort worksheets? I have Excel 2007 and I can't see an option to do that.