Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 90
Default How to put worksheets in Alphabetical order?

Is it possible to put worksheets in Alphabetical order?

Thank you.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,058
Default How to put worksheets in Alphabetical order?

See:

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


--
Gary''s Student - gsnu200765


"LiveUser" wrote:

Is it possible to put worksheets in Alphabetical order?

Thank you.

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default How to put worksheets in Alphabetical order?

Look here

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

Mike

"LiveUser" wrote:

Is it possible to put worksheets in Alphabetical order?

Thank you.

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 90
Default How to put worksheets in Alphabetical order?

I don't normally use VBA so if you could give me a little boost on a vague
step by step I would appreciate.

Where do I start and where do I enter.

Thank you.

"Gary''s Student" wrote:

See:

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


--
Gary''s Student - gsnu200765


"LiveUser" wrote:

Is it possible to put worksheets in Alphabetical order?

Thank you.

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7,247
Default How to put worksheets in Alphabetical order?

Open the VBA editor by pressing ALT F11. In the editor, select your
workbook's project in the "tree view" on the left side of the screen (CTLR R
to view it if it is hidden). Then go to the Insert menu and choose Module.
In that code window, paste the code from www.cpearson.com/Excel/SortWB.aspx.

Once you have the code in the module, close the editor from the File menu to
return to Excel. There, press ALT F8 to display the available VBA
procedures, select the item and click RUN.

--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


"LiveUser" wrote in message
...
I don't normally use VBA so if you could give me a little boost on a vague
step by step I would appreciate.

Where do I start and where do I enter.

Thank you.

"Gary''s Student" wrote:

See:

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


--
Gary''s Student - gsnu200765


"LiveUser" wrote:

Is it possible to put worksheets in Alphabetical order?

Thank you.




  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 90
Default How to put worksheets in Alphabetical order?

Chip,

Do I enter all of this in just like this?
and thank you for responding.

Attribute VB_Name = "modSortingWorksheets"
Option Explicit
Option Compare Text


Public Function SortWorksheetsByName(ByVal FirstToSort As Long, ByVal
LastToSort As Long, _
ByRef ErrorText As String, Optional ByVal SortDescending As Boolean =
False) As Boolean
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
' SortWorksheetsByName
' This sorts the worskheets from FirstToSort to LastToSort by name
' in either ascending (default) or descending order. If successful,
' ErrorText is vbNullString and the function returns True. If
' unsuccessful, ErrorText gets the reason why the function failed
' and the function returns False.
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''
Dim M As Long
Dim N As Long
Dim WB As Workbook
Dim B As Boolean

Set WB = Worksheets.Parent
ErrorText = vbNullString

If WB.ProtectStructure = True Then
ErrorText = "Workbook is protected."
SortWorksheetsByName = False
End If

'''''''''''''''''''''''''''''''''''''''''''''''
' If First and Last are both 0, sort all sheets.
'''''''''''''''''''''''''''''''''''''''''''''''
If (FirstToSort = 0) And (LastToSort = 0) Then
FirstToSort = 1
LastToSort = WB.Wo.Count
Else
'''''''''''''''''''''''''''''''''''''''
' More than one sheet selected. We
' can sort only if the selected
' sheet are adjacent.
'''''''''''''''''''''''''''''''''''''''
B = TestFirstLastSort(FirstToSort, LastToSort, ErrorText)
If B = False Then
SortWorksheetsByName = False
Exit Function
End If
End If

'''''''''''''''''''''''''''''''''''''''''''''
' Do the sort, essentially a Bubble Sort.
'''''''''''''''''''''''''''''''''''''''''''''
For M = FirstToSort To LastToSort
For N = M To LastToSort
If SortDescending = True Then
If StrComp(WB.Worksheets(N).Name, WB.Worksheets(M).Name,
vbTextCompare) 0 Then
WB.Worksheets(N).Move befo=WB.Worksheets(M)
End If
Else
If StrComp(WB.Worksheets(N).Name, WB.Worksheets(M).Name,
vbTextCompare) < 0 Then
WB.Worksheets(N).Move befo=WB.Worksheets(M)
End If
End If
Next N
Next M

SortWorksheetsByName = True

End Function

"Chip Pearson" wrote:

Open the VBA editor by pressing ALT F11. In the editor, select your
workbook's project in the "tree view" on the left side of the screen (CTLR R
to view it if it is hidden). Then go to the Insert menu and choose Module.
In that code window, paste the code from www.cpearson.com/Excel/SortWB.aspx.

Once you have the code in the module, close the editor from the File menu to
return to Excel. There, press ALT F8 to display the available VBA
procedures, select the item and click RUN.

--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


"LiveUser" wrote in message
...
I don't normally use VBA so if you could give me a little boost on a vague
step by step I would appreciate.

Where do I start and where do I enter.

Thank you.

"Gary''s Student" wrote:

See:

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


--
Gary''s Student - gsnu200765


"LiveUser" wrote:

Is it possible to put worksheets in Alphabetical order?

Thank you.


  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 90
Default How to put worksheets in Alphabetical order?

I found another example online that worked fine.

Thank you for you help everyone.

Option Explicit

Sub SortWorksheets()

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

'Change the 1 to the worksheet you want sorted first
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



"LiveUser" wrote:

Chip,

Do I enter all of this in just like this?
and thank you for responding.

Attribute VB_Name = "modSortingWorksheets"
Option Explicit
Option Compare Text


Public Function SortWorksheetsByName(ByVal FirstToSort As Long, ByVal
LastToSort As Long, _
ByRef ErrorText As String, Optional ByVal SortDescending As Boolean =
False) As Boolean
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
' SortWorksheetsByName
' This sorts the worskheets from FirstToSort to LastToSort by name
' in either ascending (default) or descending order. If successful,
' ErrorText is vbNullString and the function returns True. If
' unsuccessful, ErrorText gets the reason why the function failed
' and the function returns False.
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''
Dim M As Long
Dim N As Long
Dim WB As Workbook
Dim B As Boolean

Set WB = Worksheets.Parent
ErrorText = vbNullString

If WB.ProtectStructure = True Then
ErrorText = "Workbook is protected."
SortWorksheetsByName = False
End If

'''''''''''''''''''''''''''''''''''''''''''''''
' If First and Last are both 0, sort all sheets.
'''''''''''''''''''''''''''''''''''''''''''''''
If (FirstToSort = 0) And (LastToSort = 0) Then
FirstToSort = 1
LastToSort = WB.Wo.Count
Else
'''''''''''''''''''''''''''''''''''''''
' More than one sheet selected. We
' can sort only if the selected
' sheet are adjacent.
'''''''''''''''''''''''''''''''''''''''
B = TestFirstLastSort(FirstToSort, LastToSort, ErrorText)
If B = False Then
SortWorksheetsByName = False
Exit Function
End If
End If

'''''''''''''''''''''''''''''''''''''''''''''
' Do the sort, essentially a Bubble Sort.
'''''''''''''''''''''''''''''''''''''''''''''
For M = FirstToSort To LastToSort
For N = M To LastToSort
If SortDescending = True Then
If StrComp(WB.Worksheets(N).Name, WB.Worksheets(M).Name,
vbTextCompare) 0 Then
WB.Worksheets(N).Move befo=WB.Worksheets(M)
End If
Else
If StrComp(WB.Worksheets(N).Name, WB.Worksheets(M).Name,
vbTextCompare) < 0 Then
WB.Worksheets(N).Move befo=WB.Worksheets(M)
End If
End If
Next N
Next M

SortWorksheetsByName = True

End Function

"Chip Pearson" wrote:

Open the VBA editor by pressing ALT F11. In the editor, select your
workbook's project in the "tree view" on the left side of the screen (CTLR R
to view it if it is hidden). Then go to the Insert menu and choose Module.
In that code window, paste the code from www.cpearson.com/Excel/SortWB.aspx.

Once you have the code in the module, close the editor from the File menu to
return to Excel. There, press ALT F8 to display the available VBA
procedures, select the item and click RUN.

--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


"LiveUser" wrote in message
...
I don't normally use VBA so if you could give me a little boost on a vague
step by step I would appreciate.

Where do I start and where do I enter.

Thank you.

"Gary''s Student" wrote:

See:

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


--
Gary''s Student - gsnu200765


"LiveUser" wrote:

Is it possible to put worksheets in Alphabetical order?

Thank you.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how can i re-sort multiple worksheets into alphabetical order Stuart Excel Discussion (Misc queries) 3 May 22nd 10 07:35 AM
How do I put worksheets in alphabetical order DLee New Users to Excel 2 July 27th 05 09:16 PM
How do I put worksheets in alphabetical order DLee Excel Worksheet Functions 1 July 27th 05 08:41 PM
how do i sort excel worksheets by alphabetical order? Birichica Excel Worksheet Functions 4 February 23rd 05 06:10 PM
how can i sort excel worksheets in alphabetical order? Birichica Excel Worksheet Functions 1 February 14th 05 07:25 PM


All times are GMT +1. The time now is 06:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"