3
I need to reorder my sheets in a specific order, then if there are any left, do it alphabetically. I have the macro below to reorder them alphabetically.
If i have sheets ""METALS", "SVOC", "GENCHEM", etc.-- I want those to always be in alphabetical order. THEN any other sheets "Apple", "zebra", "Lion" should come after in alphabetical order.
I tried this code, but didn't work out
Sheets("GENCHEM").Move Before:=Sheets(1)
Sheets("METALS").Move Before:=Sheets(2)
Sheets("PCBS").Move Before:=Sheets(3)
Sheets("OC_PEST").Move Before:=Sheets(4)
Sheets("SVOC").Move Before:=Sheets(5)
Sheets("VOC").Move Before:=Sheets(6)
'-------My working macro below----
Option Explicit
Sub reordersheets()
'---Reorders the Sheets---
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 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
Welcome to the site. What version of Excel (assuming that is what you are using)? – CharlieRB – 2017-02-15T16:18:56.643
Your question is ambiguous - your sample orders
PCBS
in front ofOC_PEST
- that's not alphabetically. – LotPings – 2017-02-15T17:08:03.207