How do I arrange sheets in alphabetical order?

S

Supreme Grace

I would like to have more than 60 sheets in a excel file, but going to
particular sheet is very difficult for me. so i want to search a particular
sheet or to arrang the sheets in alphabetical order
 
K

Kassie

Why 60 sheets?
You can write 60 little macros -
Sub Macro1()
Sheets("Sheetn").Select
End Sub

Where "n" = the sheet you want to go to. You can also replace "Sheetn" with
the name of the sheet (In ""). I just don't know how you are going to
remember the names of all these macro's! You can use Ctrla to Ctrlz and
CtrlShifta to CtrlShiftz, but that is only 56.

Why 60 sheets? How big is each sheet?

Wow
 
G

Gord Dibben

Grace

For the sorting part you have a link to Chip's site.

For the selecting part............

If you right-click on the sheet navigation arrows at bottom left of status bar
you will get a pop-up with 16 sheets on it and a "more sheets" button.

Otherwise you could create a Table of Contents sheet with selectable sheet
names.

See David McRitchie's site for a TOC macro.

http://www.mvps.org/dmcritchie/excel/buildtoc.htm

Or use Bob Phillips BrowseSheets macro(posted below)which displays a form with
option buttons to select a sheet. I personally like this one.

Sub BrowseSheets()
Const nPerColumn As Long = 38 'number of items per column
Const nWidth As Long = 13 'width of each letter
Const nHeight As Long = 18 'height of each row
Const sID As String = "___SheetGoto" 'name of dialog sheet
Const kCaption As String = " Select sheet to goto"
'dialog caption

Dim i As Long
Dim TopPos As Long
Dim iBooks As Long
Dim cCols As Long
Dim cLetters As Long
Dim cMaxLetters As Long
Dim cLeft As Long
Dim thisDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As OptionButton

Application.ScreenUpdating = False

If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If

On Error Resume Next
Application.DisplayAlerts = False
ActiveWorkbook.DialogSheets(sID).Delete
Application.DisplayAlerts = True
On Error GoTo 0
Set CurrentSheet = ActiveSheet
Set thisDlg = ActiveWorkbook.DialogSheets.Add

With thisDlg

.Name = sID
.Visible = xlSheetHidden

'sets variables for positioning on dialog
iBooks = 0
cCols = 0
cMaxLetters = 0
cLeft = 78
TopPos = 40

For i = 1 To ActiveWorkbook.Worksheets.Count

If i Mod nPerColumn = 1 Then
cCols = cCols + 1
TopPos = 40
cLeft = cLeft + (cMaxLetters * nWidth)
cMaxLetters = 0
End If

Set CurrentSheet = ActiveWorkbook.Worksheets(i)
cLetters = Len(CurrentSheet.Name)
If cLetters > cMaxLetters Then
cMaxLetters = cLetters
End If

iBooks = iBooks + 1
.OptionButtons.Add cLeft, TopPos, cLetters * nWidth, 16.5
.OptionButtons(iBooks).text = _
ActiveWorkbook.Worksheets(iBooks).Name
TopPos = TopPos + 13

Next i

.Buttons.Left = cLeft + (cMaxLetters * nWidth) + 24

CurrentSheet.Activate

With .DialogFrame
.Height = Application.Max(68, _
Application.Min(iBooks, nPerColumn) * nHeight + 10)
.Width = cLeft + (cMaxLetters * nWidth) + 24
.Caption = kCaption
End With

.Buttons("Button 2").BringToFront
.Buttons("Button 3").BringToFront

Application.ScreenUpdating = True
If .Show Then
For Each cb In thisDlg.OptionButtons
If cb.Value = xlOn Then
ActiveWorkbook.Worksheets(cb.Caption).Select
Exit For
End If
Next cb
Else
MsgBox "Nothing selected"
End If

Application.DisplayAlerts = False
.Delete
End With
End Sub


Gord Dibben Excel MVP

On Tue, 15 Mar 2005 23:49:06 -0800, "Supreme Grace" <Supreme
 
Top