Sorting tabs

[ElvisSteel]

Hi,

I have a large workbook with lots of sheets and I need to be able to sort the sheets so they are alphabetical from left to right, i.e. sheet "AA", then "AB", then "BA" etc.
Is there a way to do this?

Thanks

Steve

 

[Robert Mika]

Look here:

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

 

[Jon von der Heyden]

Hi Steve

Try;

[COLOR="Blue"]Public[/COLOR] [COLOR="Blue"]Sub[/COLOR] SortSheets()
    [COLOR="Blue"]Const[/COLOR] lngMaxChars [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Long[/COLOR] = 30
    
    [COLOR="Blue"]Dim[/COLOR] lngChar [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Long[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] varNum [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Variant[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] wks1 [COLOR="Blue"]As[/COLOR] Worksheet, wks2 [COLOR="Blue"]As[/COLOR] Worksheet
    [COLOR="Blue"]Dim[/COLOR] strName1 [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR], strName2 [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]
    
    [COLOR="Blue"]If[/COLOR] ThisWorkbook.Worksheets.Count = 1 [COLOR="Blue"]Then[/COLOR] [COLOR="Blue"]GoTo[/COLOR] Finish
    
    [COLOR="Blue"]For[/COLOR] [COLOR="Blue"]Each[/COLOR] wks1 [COLOR="Blue"]In[/COLOR] ThisWorkbook.Worksheets
        varNum = ""
        [COLOR="Blue"]For[/COLOR] lngChar = [COLOR="Blue"]Len[/COLOR](wks1.Name) [COLOR="Blue"]To[/COLOR] 1 [COLOR="Blue"]Step[/COLOR] -1
            [COLOR="Blue"]If[/COLOR] [COLOR="Blue"]Not[/COLOR] [COLOR="Blue"]IsNumeric[/COLOR]([COLOR="Blue"]Mid[/COLOR](wks1.Name, lngChar, 1)) [COLOR="Blue"]Then[/COLOR]
                [COLOR="Blue"]Exit[/COLOR] [COLOR="Blue"]For[/COLOR]
            [COLOR="Blue"]Else[/COLOR]
                varNum = [COLOR="Blue"]Mid[/COLOR](wks1.Name, lngChar, 1) & varNum
            [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
        [COLOR="Blue"]Next[/COLOR] lngChar
        strName1 = [COLOR="Blue"]Left$[/COLOR](wks1.Name, [COLOR="Blue"]Len[/COLOR](wks1.Name) - [COLOR="Blue"]Len[/COLOR](varNum))
        strName1 = strName1 & [COLOR="Blue"]Format$[/COLOR]([COLOR="Blue"]Val[/COLOR](0 & varNum), Application.Rept(0, lngMaxChars - [COLOR="Blue"]Len[/COLOR](strName1)))
        [COLOR="Blue"]For[/COLOR] [COLOR="Blue"]Each[/COLOR] wks2 [COLOR="Blue"]In[/COLOR] ThisWorkbook.Worksheets
            varNum = ""
            [COLOR="Blue"]For[/COLOR] lngChar = [COLOR="Blue"]Len[/COLOR](wks2.Name) [COLOR="Blue"]To[/COLOR] 1 [COLOR="Blue"]Step[/COLOR] -1
                [COLOR="Blue"]If[/COLOR] [COLOR="Blue"]Not[/COLOR] [COLOR="Blue"]IsNumeric[/COLOR]([COLOR="Blue"]Mid[/COLOR](wks2.Name, lngChar, 1)) [COLOR="Blue"]Then[/COLOR]
                    [COLOR="Blue"]Exit[/COLOR] [COLOR="Blue"]For[/COLOR]
                [COLOR="Blue"]Else[/COLOR]
                    varNum = [COLOR="Blue"]Mid[/COLOR](wks2.Name, lngChar, 1) & varNum
                [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
            [COLOR="Blue"]Next[/COLOR] lngChar
            strName2 = [COLOR="Blue"]Left$[/COLOR](wks2.Name, [COLOR="Blue"]Len[/COLOR](wks2.Name) - [COLOR="Blue"]Len[/COLOR](varNum))
            strName2 = strName2 & [COLOR="Blue"]Format$[/COLOR]([COLOR="Blue"]Val[/COLOR](0 & varNum), Application.Rept(0, lngMaxChars - [COLOR="Blue"]Len[/COLOR](strName2)))
            [COLOR="Blue"]If[/COLOR] [COLOR="Blue"]StrComp[/COLOR](strName1, strName2, 1) = 1 [COLOR="Blue"]Then[/COLOR]
                wks1.Move after:=wks2
            [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
        [COLOR="Blue"]Next[/COLOR] wks2
    [COLOR="Blue"]Next[/COLOR] wks1
Finish:
    [COLOR="Blue"]Set[/COLOR] wks1 = [COLOR="Blue"]Nothing[/COLOR]
    [COLOR="Blue"]Set[/COLOR] wks2 = [COLOR="Blue"]Nothing[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[VoG]

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