sorting worksheets in workbook

[pet...@outer-net.com]

Hi there,

I was wondering if there is an easy way to sort the worksheets within
a workbook in Excel 7

I have been doing it individually upto now, but as I create more and
more worksheets within a given workbook, it is becoming more
diffulcult

Thank you

Peter Gordon

[Ananth]

Hi

Use this... It should serve the purpose

Sub mSortSheets()

'This routine sorts the shheets of the active workbook in ascending
order
Dim SheetCount As Integer
Dim snCount As Integer
Dim VisibleWins As Integer
Dim SheetNames() As String
Dim OldActive As Object

'Don't update the screen
Application.ScreenUpdating = False

'Disable the Ctrl+Break
Application.EnableCancelKey = xlDisabled

'Count the number of open windows
VisibleWins = 0
For Each Item In Windows
If Item.Visible Then
VisibleWins = VisibleWins + 1
End If
Next Item

'Exit if no workbook is open
If VisibleWins = 0 Then
Exit Sub
End If

'Workbook available
'Check if the workbook is protected
If ActiveWorkbook.ProtectStructure Then
MsgBox ActiveWorkbook.Name & " is protected", vbCritical, _
"Cannot Sort Sheets"
Exit Sub
End If

'Get the number of sheets & redimension the array
SheetCount = ActiveWorkbook.Sheets.Count
ReDim SheetNames(1 To SheetCount)

'Store the active sheet
Set OldActive = ActiveSheet

'Fill the array with the sheet names
For snCount = 1 To SheetCount
SheetNames(snCount) = ActiveWorkbook.Sheets(snCount).Name
Next snCount

'Sort the array in ascending order
Call BubbleSort(SheetNames)

'Move the sheets to the respective locations
For snCount = 1 To SheetCount
ActiveWorkbook.Sheets(SheetNames(snCount)).Move
ActiveWorkbook.Sheets(snCount)
Next snCount

'Reactivate the origincal active sheet
OldActive.Activate

End Sub
-----------------------
Sub BubbleSort(List() As String)

'Sorts the List array in ascending order
Dim First As Integer, Last As Integer
Dim i As Integer, j As Integer
Dim Temp

First = LBound(List)
Last = UBound(List)
For i = First To Last - 1
For j = i + 1 To Last
If UCase(List(i)) > UCase(List(j)) Then
Temp = List(j)
List(j) = List(i)
List(i) = Temp
End If
Next j
Next i

End Sub
---------------------------------

pet...@outer-net.com wrote in article
<3415b9cc...@news.outer-net.com>...

[Jerry Hunt]

pet...@outer-net.com wrote:
>
> Hi there,
>
> I was wondering if there is an easy way to sort the worksheets within
> a workbook in Excel 7
>
> I have been doing it individually upto now, but as I create more and
> more worksheets within a given workbook, it is becoming more
> diffulcult
>
> Thank you
>
> Peter Gordon

========================================================================
In John Walkenbach's book "Excel for Windows 95 Power Programming with
VBA', there is a VBA sub for sorting the worksheets. The book comes with
a CD-ROM, which includes that VBA sub. Without his permission, I think
it would be unfair (and perhaps a violiation of copyright laws) for me
to reproduce it here. If you plan on doing much VBA coding, you should
have this book. (No, I have no connection with John or the publisher of
the book).
--
Spam buster:
To send me e-mail, take the NoSpam out of my e-mail address

[Bill Manville]

On Tue, 09 Sep 1997 21:06:23 GMT, pet...@outer-net.com wrote:

>Hi there,
>
>I was wondering if there is an easy way to sort the worksheets within
>a workbook in Excel 7
>
>I have been doing it individually upto now, but as I create more and
>more worksheets within a given workbook, it is becoming more
>diffulcult
>

Sub SortSheets()
Dim iSheet As Integer, iBefore As Integer
For iSheet = 1 To ActiveWorkbook.Worksheets.Count
Worksheets(iSheet).Visible = True
For iBefore = 1 To iSheet - 1
If UCase(Worksheets(iBefore).Name) >
UCase(Worksheets(iSheet).Name) Then
ActiveWorkbook.Worksheets(iSheet).Move
before:=ActiveWorkbook.Worksheets(iBefore)
Exit For
End If
Next iBefore
Next iSheet
End Sub

Bill Manville
Oxford, England
Microsoft Excel - MVP