Sort Worksheets
 

Is there any way (using VBA code) to place all WorkSheets in a WorkBook in
alphabetical order?

Sort Worksheets
 

you can give this a try, i think it was chip pearson's code:

Sub SortWorksheets()
'Chip Pearson

Dim N As Integer
Dim M As Integer
Dim FirstWSToSort As Integer
Dim LastWSToSort As Integer
Dim SortDescending As Boolean

SortDescending = False

If ActiveWindow.SelectedSheets.Count = 1 Then
FirstWSToSort = 1
LastWSToSort = Worksheets.Count
Else
With ActiveWindow.SelectedSheets
For N = 2 To .Count
If .Item(N - 1).Index < .Item(N).Index - 1 Then
MsgBox "You cannot sort non-adjacent sheets"
Exit Sub
End If
Next N
FirstWSToSort = .Item(1).Index
LastWSToSort = .Item(.Count).Index
End With
End If

For M = FirstWSToSort To LastWSToSort
For N = M To LastWSToSort
If SortDescending = True Then
If UCase(Worksheets(N).Name) _
UCase(Worksheets(M).Name) Then
Worksheets(N).Move Befo=Worksheets(M)
End If
Else
If UCase(Worksheets(N).Name) < _
UCase(Worksheets(M).Name) Then
Worksheets(N).Move Befo=Worksheets(M)
End If
End If
Next N
Next M

End Sub




--

Gary


"Patrick C. Simonds" wrote in message
...
Is there any way (using VBA code) to place all WorkSheets in a WorkBook in
alphabetical order?

Sort Worksheets
 

Hi Patrick.

see Chip Pearson at:


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


my friend Norman Jones proposed a little variation to the good Chip
code:

instead of:
-----------------------------------------------------------------
If SortDescending = True Then
If UCase(Worksheets(N).Name) UCase(Worksheets(M).Name) Then
Worksheets(N).Move Befo=Worksheets(M)
End If
Else
If UCase(Worksheets(N).Name) < UCase(Worksheets(M).Name) Then
Worksheets(N).Move Befo=Worksheets(M)
End If
End If
-----------------------------------------------------------------

try:

If (UCase(Worksheets(N).Name) UCase(Worksheets(M).Name)) = _
SortDescending Then
Worksheets(N).Move Befo=Worksheets(M)
End If

Regards
Eliano


On 24 Feb, 02:25, "Patrick C. Simonds" wrote:
Is there any way (using VBA code) to place all WorkSheets in a WorkBook in
alphabetical order?

Sort Worksheets
 

I've not tried this, but you should be able to find something on Chip
Pearson's site

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

--
HTH,
Barb Reinhardt



"Patrick C. Simonds" wrote:

Is there any way (using VBA code) to place all WorkSheets in a WorkBook in
alphabetical order?

Sort Worksheets
 

Quick and dirty sort by name.

Sub sort_sheets()
'Mike H June 13th, 2007
Dim I As Integer, J As Integer

For I = 1 To Sheets.Count - 1
For J = I + 1 To Sheets.Count
If UCase(Sheets(I).Name) UCase(Sheets(J).Name) Then
Sheets(J).Move Befo=Sheets(I)
End If
Next J
Next I
End Sub

For more methods and flexibility see Chip pearson's site.

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


Gord Dibben MS Excel MVP

On Sat, 23 Feb 2008 17:25:08 -0800, "Patrick C. Simonds"
wrote:

Is there any way (using VBA code) to place all WorkSheets in a WorkBook in
alphabetical order?

Sort Worksheets
 

You can use the following macro:

Sub SheetSortMacro()

' Macro to work with chartsheets also

Dim shtCount As Long
Dim i As Long, j As Long

On Error GoTo MacroError

shtCount = ActiveWorkbook.Sheets.Count

For i = 1 To shtCount - 1
For j = i + 1 To shtCount
If UCase(Sheets(j).Name) < UCase(Sheets(i).Name) Then
Sheets(j).Move Befo=Sheets(i)
End If
Next j
Next i

ExitHe
Exit Sub

MacroError:
MsgBox Err.Description
Resume ExitHere
End Sub

--
Regards,
Anant


"Patrick C. Simonds" wrote:

Is there any way (using VBA code) to place all WorkSheets in a WorkBook in
alphabetical order?