organizing a workbook
 

I have a workbook with about 150 sheets and would like to alphabetize them.
How do I do this. Also is there a find function to find a specific sheet
without scrolling thru them? And when I set up 1 sheet with named lines and
columns how can I apply this to all the sheets at once instead of copy and
paste one at a time. Thanks for your imput, Bill I'm using office 2000

Hi Bill,
For sort worksheets in text order see:
http://www.mvps.org/dmcritchie/excel...#sortallsheets

To find a specific sheet is a lot easier if the sheets have been sorted as above.
Right click on a sheet navigation arrow to the left of the sheetnames, choose
your sheet or choose "more sheets", There is a macro to help you get to
"more sheets" faster.
http://www.mvps.org/dmcritchie/excel...htm#MoreSheets

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"mrbillmsn" wrote in message ...
I have a workbook with about 150 sheets and would like to alphabetize them.
How do I do this. Also is there a find function to find a specific sheet
without scrolling thru them? And when I set up 1 sheet with named lines and
columns how can I apply this to all the sheets at once instead of copy and
paste one at a time. Thanks for your imput, Bill I'm using office 2000

I don't know the answer to the last part if that is supposed to be a different
range on each sheet..

"mrbillmsn" wrote
-And when I set up 1 sheet with named lines and
columns how can I apply this to all the sheets at once instead of copy and
paste one at a time. Thanks for your imput, Bill I'm using office 2000

Bill

1. Sorting sheets will require VBA.

2. With 150 sheets you would need a sheet lister with selection
buttons....VBA again.

3. Right-click and "Select All Sheets" to "group" them all.

Make your changes on one sheet and will be done to the rest in the "group"

DO NOT FORGET to right-click and "ungroup" when changes are complete.

For the sorting sheets code see Chip Pearson's site.

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

For the sheet lister I like Bob Phillips' code.......below.

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 Mon, 12 Sep 2005 15:03:01 -0700, mrbillmsn
wrote:

I have a workbook with about 150 sheets and would like to alphabetize them.
How do I do this. Also is there a find function to find a specific sheet
without scrolling thru them? And when I set up 1 sheet with named lines and
columns how can I apply this to all the sheets at once instead of copy and
paste one at a time. Thanks for your imput, Bill I'm using office 2000