|
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
|||
|
|||
|
Sorting worksheets in Excel
Hello all,
The workbook I am using has about 60 worksheets. I have found subroutines that will sort either by tab color or by worksheet name alphabetically. I would like to sort by tab color, then by name in one subroutine. Can this be accomplished? Thanks for any help, Steve |
|
#2
|
|||
|
|||
|
These are the two subroutines I have found. Can they be combined or re-written into one?
Here is the first: Sub GroupSheetsByColor() Dim lCount As Long, lCounted As Long Dim lShtLast As Long lShtLast = Sheets.Count For lCount = 1 To lShtLast For lCounted = lCount To lShtLast If Sheets(lCounted).Tab.ColorIndex = Sheets(lCount).Tab.ColorIndex Then Sheets(lCounted).Move Before:=Sheets(lCount) End If Next lCounted Next lCount End Sub Here is the second: (This one I can select one color group at a time) Sub SortWorksheets() 'If I sort by color first, I can then select the' ' color group and use this subroutine' Dim N As Integer Dim M As Integer Dim FirstWSToSort As Integer Dim LastWSToSort As Integer Dim SortDescending As Boolean Dim AscDesc AscDesc = MsgBox(prompt:="Sort worksheets in ascending order?", _ Title:="Sort Order", _ Buttons:=vbYesNoCancel) If AscDesc = vbYes Then SortDescending = False ElseIf AscDesc = vbNo Then SortDescending = True Else Exit Sub End If 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 Before:=Worksheets(M) End If Else If UCase(Worksheets(N).Name) < UCase(Worksheets(M).Name) Then Worksheets(N).Move Before:=Worksheets(M) End If End If Next N Next M End Sub Steve |
![]() |
| Viewing: ASP Free Forums > Programming > Visual Basic Programming > Sorting worksheets in Excel |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|