+ Reply to Thread
Results 1 to 3 of 3

Sorting an array of numbers from Low to High for worksheet Tabs

Hybrid View

  1. #1
    Valued Forum Contributor realniceguy5000's Avatar
    Join Date
    03-20-2008
    Location
    Fl
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    951

    Sorting an array of numbers from Low to High for worksheet Tabs

    Hi, Searching around this morning trying to find something that may work, I need to sort my worksheet tabs from 1 to 62 so I tried many scripts but the sort is always in the wrong order. The tab names are 1,2,3 all the way to 62. So I thought I could put these in an array and then sort the array then sort the tabs by the sorted array. Just not working?

    could someone help with this script below or maybe another idea would work.

    Thank You, Mike

    
    Sub SortWS3()
    Dim Ndx As Long
    Dim i
        ReDim SNarray(1 To Sheets.Count)
        For i = 1 To Sheets.Count
            SNarray(i) = ThisWorkbook.Sheets(i).Name
    'Debug.Print SNarray(i)
        Next
        Stop
        
     Call BubbleSort(SNarray)
    
    For Ndx = UBound(SNarray) To LBound(SNarray) Step -1
        Worksheets(SNarray(Ndx)).Move before:=Worksheets(1)
    Next Ndx
    
    End Sub
    Sub BubbleSort(ByRef lngArray() As Long)
        Dim iOuter As Long
        Dim iInner As Long
        Dim iLBound As Long
        Dim iUBound As Long
        Dim iTemp As Long
        
        iLBound = LBound(lngArray)
        iUBound = UBound(lngArray)
        
        'Which bubbling pass
        For iOuter = iLBound To iUBound - 1
            'Which comparison
            For iInner = iLBound To iUBound - iOuter - 1
            
                'Compare this item to the next item
                If lngArray(iInner) > lngArray(iInner + 1) Then
                    'Swap
                    iTemp = lngArray(iInner)
                    lngArray(iInner) = lngArray(iInner + 1)
                    lngArray(iInner + 1) = iTemp
                End If
                
            Next iInner
        Next iOuter
    End Sub
    Last edited by realniceguy5000; 04-25-2013 at 11:58 AM.

  2. #2
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Sorting an array of numbers from Low to High for worksheet Tabs

    Sub M_snb()
        For j = 1 To Sheets.Count
            Sheets(Format(j)).Move Sheets(j)
        Next
    End Sub



  3. #3
    Valued Forum Contributor realniceguy5000's Avatar
    Join Date
    03-20-2008
    Location
    Fl
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    951

    Re: Sorting an array of numbers from Low to High for worksheet Tabs

    Simply Amazing. I see your still the winner of the smallest code that works. :-) Nice to hear from you again...

    Thanks as always, Mike

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1