Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort Worksheets
Is there any way (using VBA code) to place all WorkSheets in a WorkBook in
alphabetical order? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort Worksheets | Excel Discussion (Misc queries) | |||
how to sort worksheets | Excel Programming | |||
Sort worksheets | Excel Programming | |||
Sort Worksheets | Excel Worksheet Functions | |||
Sort Numerically Worksheets via VB when creating a new worksheets | Excel Programming |