Sorting Tabs

[Nadia1182]

Hello,

I was wondering if there is a macro I can use to sort tabs in numberical order.

Thank you

 

[kidwispa]

Found on another website...

[COLOR=#0000ff]Option Explicit[/COLOR] 
 
[COLOR=blue]Sub[/COLOR] SortWorksheets() 
 
    [COLOR=blue]Dim[/COLOR] N [COLOR=blue]As[/COLOR] [COLOR=blue]Integer[/COLOR] 
    [COLOR=blue]Dim[/COLOR] M [COLOR=blue]As[/COLOR] [COLOR=blue]Integer[/COLOR] 
    [COLOR=blue]Dim[/COLOR] FirstWSToSort [COLOR=blue]As[/COLOR] [COLOR=blue]Integer[/COLOR] 
    [COLOR=blue]Dim[/COLOR] LastWSToSort [COLOR=blue]As[/COLOR] [COLOR=blue]Integer[/COLOR] 
    [COLOR=blue]Dim[/COLOR] SortDescending [COLOR=blue]As[/COLOR] [COLOR=blue]Boolean[/COLOR] 
 
    SortDescending = [COLOR=blue]False[/COLOR] 
 
    [COLOR=blue]If[/COLOR] ActiveWindow.SelectedSheets.Count = 1 [COLOR=blue]Then[/COLOR] 
 
         [COLOR=darkgreen]'Change the 1 to the worksheet you want sorted first[/COLOR]
        FirstWSToSort = 1 
        LastWSToSort = Worksheets.Count 
    [COLOR=blue]Else[/COLOR] 
        [COLOR=blue]With[/COLOR] ActiveWindow.SelectedSheets 
            [COLOR=blue]For[/COLOR] N = 2 [COLOR=blue]To[/COLOR] .Count 
                [COLOR=blue]If[/COLOR] .Item(N - 1).Index <> .Item(N).Index - 1 [COLOR=blue]Then[/COLOR] 
                    MsgBox "You cannot sort non-adjacent sheets" 
                    Exit [COLOR=blue]Sub[/COLOR] 
                [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR] 
            [COLOR=blue]Next[/COLOR] N 
            FirstWSToSort = .Item(1).Index 
            LastWSToSort = .Item(.Count).Index 
        [COLOR=blue]End With[/COLOR] 
    [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR] 
 
    [COLOR=blue]For[/COLOR] M = FirstWSToSort [COLOR=blue]To[/COLOR] LastWSToSort 
        [COLOR=blue]For[/COLOR] N = M [COLOR=blue]To[/COLOR] LastWSToSort 
            [COLOR=blue]If[/COLOR] SortDescending = [COLOR=blue]True[/COLOR] [COLOR=blue]Then[/COLOR] 
                [COLOR=blue]If[/COLOR] UCase(Worksheets(N).Name) > UCase(Worksheets(M).Name) [COLOR=blue]Then[/COLOR] 
                    Worksheets(N).Move Before:=Worksheets(M) 
                [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR] 
            [COLOR=blue]Else[/COLOR] 
                [COLOR=blue]If[/COLOR] UCase(Worksheets(N).Name) < UCase(Worksheets(M).Name) [COLOR=blue]Then[/COLOR] 
                    Worksheets(N).Move Before:=Worksheets(M) 
                [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR] 
            [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR] 
        [COLOR=blue]Next[/COLOR] N 
    [COLOR=blue]Next[/COLOR] M 
 
[COLOR=blue]End Sub[/COLOR]

 

[Nadia1182]

Thank you kidwispa for your help. I am trying your macro and am getting the following error message.

"You can not sort non-adjacent sheets."

What could I be doing wrong?

 

[kidwispa]

Hi Nadia,

The code below sorts worksheets into alphabetical order - I have tried it and it seems to work ok? I got it from

http://www.vbaexpress.com/kb/getarticle.php?kb_id=72

 

[Nadia1182]

The tabs I have are numbered from 10000 – 10123 yet they are all mixed up. I want to put them in order numerically. Perhaps since this macro alphabetizes this is why it isn’t working?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[VoG]

Try ASAP Utilities http://www.asap-utilities.com/asap-utilities-excel-tools-tip.php?tip=32&utilities=20&lang=en_us

 

[Nadia1182]

Thanks Peter, this would be perfect however I don't have admin rights on my computer so I can't download the software.

A macro is my only work around.