Rearrange sheets on tab numbers

[Jos vd Heijden]

Is it possible to rearrange the sheets on the numbers of the tabs.
I want to have the sheet with the lowest number of the tab on the left to start with.

[Debraj Roy]

check here for more details..

http://www.ozgrid.com/VBA/sort-sheets.htm

[Trebor76]

Hi Jos,

There's plenty of references on how to do this on the internet including this article by Pearson Software Consulting.

That said, I wrote this to sort numeric sheet tabs as in your example as well:

Option Explicit
Sub SortNumericTabs()
    
    Dim varMyArray() As Variant 'Declares a dynamic array variable
    Dim lngArrayCount As Long
    Dim wsMySheet As Worksheet
    
    For Each wsMySheet In ThisWorkbook.Sheets
        lngArrayCount = lngArrayCount + 1
        ReDim Preserve varMyArray(1 To lngArrayCount) 'Append the record to the existing array
        varMyArray(lngArrayCount) = Val(wsMySheet.Name)
    Next wsMySheet
    
    varMyArray = BubbleSrt(varMyArray, True) 'True - sort 'varMyArray' Ascending. False - sort 'varMyArray' Decending.
    
    'Loop through Array elements
    For lngArrayCount = 1 To UBound(varMyArray)
        For Each wsMySheet In ThisWorkbook.Sheets
            If wsMySheet.Name = CStr(varMyArray(lngArrayCount)) Then
                Exit For
            End If
        Next wsMySheet
        If lngArrayCount < ThisWorkbook.Worksheets.Count Then
            wsMySheet.Move before:=ThisWorkbook.Worksheets(lngArrayCount + 1)
        Else
            wsMySheet.Move after:=ThisWorkbook.Worksheets(lngArrayCount)
        End If
    Next lngArrayCount
    
    Erase varMyArray() 'Deletes the varible contents to free some memory
    
End Sub
'This UDF is from here: http://www.mrexcel.com/forum/excel-questions/690718-visual-basic-applications-sort-array-numbers.html
Public Function BubbleSrt(ArrayIn, Ascending As Boolean)

    Dim SrtTemp As Variant
    Dim i As Long
    Dim j As Long


    If Ascending = True Then
        For i = LBound(ArrayIn) To UBound(ArrayIn)
             For j = i + 1 To UBound(ArrayIn)
                 If ArrayIn(i) > ArrayIn(j) Then
                     SrtTemp = ArrayIn(j)
                     ArrayIn(j) = ArrayIn(i)
                     ArrayIn(i) = SrtTemp
                 End If
             Next j
         Next i
    Else
        For i = LBound(ArrayIn) To UBound(ArrayIn)
             For j = i + 1 To UBound(ArrayIn)
                 If ArrayIn(i) < ArrayIn(j) Then
                     SrtTemp = ArrayIn(j)
                     ArrayIn(j) = ArrayIn(i)
                     ArrayIn(i) = SrtTemp
                 End If
             Next j
         Next i
    End If
    
    BubbleSrt = ArrayIn

End Function

Regards,

Robert

[Jos vd Heijden]

Thanks, everybody.
This works for me.