How do you sort worksheets (tabs) by numerical order? any way to do this? Thanks! :)

[brad7989]

I have a few tabs that pull in and may not be in order. I would love to sort worksheets in order with the lowest number on the left and the highest on the right.

Thanks!

 

[shg]

[FONT=Consolas][COLOR=#595959]Sub SortSheets(Optional wkb As Workbook = Nothing, _[/COLOR][/FONT]
[COLOR=#595959][FONT=Consolas]             Optional ByVal iBeg As Long = 1, _[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]             Optional ByVal iEnd As Long = 2147483647)[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]  ' shg 2009-09[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]  ' Insertion-sorts sheets from iBeg to iEnd[/FONT][/COLOR]
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
[COLOR=#595959][FONT=Consolas]  Dim i           As Long[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]  Dim j           As Long[/FONT][/COLOR]
<o:p></o:p>
[COLOR=#595959][FONT=Consolas]  If wkb Is Nothing Then Set wkb = ActiveWorkbook[/FONT][/COLOR]
<o:p></o:p>
[COLOR=#595959][FONT=Consolas]  With wkb[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]      If iBeg < 1 Then iBeg = 1[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]      If iEnd > .Sheets.Count Then iEnd = .Sheets.Count[/FONT][/COLOR]
<o:p></o:p>
[COLOR=#595959][FONT=Consolas]      For i = iBeg + 1 To iEnd[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]          For j = iBeg To i - 1[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]              If StrComp(.Sheets(i).Name, .Sheets(j).Name, vbTextCompare) <> 1 Then[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]                  .Sheets(i).Move Before:=.Sheets(j)[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]                  Exit For[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]              End If[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]          Next j[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]      Next i[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]  End With[/FONT][/COLOR]
[FONT=Consolas][COLOR=#595959]End Sub[/COLOR][/FONT]

E.g., to sort all the sheets in the active workbook,

SortSheets

 

[brad7989]

Hey - thanks!

One problem - the code jams up and the debugger highlights:


"If wkb Is Nothing Then"


What should I do?

 

[shg]

How did you call the code?