I keep all my worksheets in alpha order, but have to add new ones on a regular basis. I have a macro that creates a new worksheet from the Master, which allows me to name the worksheet. At the end of that macro it calls the SortWorksheets macro. All this works fine, except at the end of the process, the focus is on the sheet before the new one. How can I make the focus on the newest worksheet?
Sub New_Worksheet() ' This Macro came from Google, I don't remember where I got it from.
' Uses the CTRL+SHIFT+W shortcut to create a new worksheet from the MASTER worksheet. Opens a dialog box for the Tab name.
Dim sName As String
Dim wks As Worksheet
Worksheets("Master").Copy After:=Worksheets("Master")
Set wks = ActiveSheet
Do While sName <> wks.Name
sName = Application.InputBox _
(Prompt:="Enter new worksheet name")
On Error Resume Next
wks.Name = sName
Range("C1").Value = sName
On Error GoTo 0
Loop
Set wks = Nothing
Call SortWorksheets
End Sub
--------------------------------------------------------
Sub SortWorksheets() 'I got this macro from an answer in this forum from 2004!
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 = 5
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
Sub New_Worksheet() ' This Macro came from Google, I don't remember where I got it from.
' Uses the CTRL+SHIFT+W shortcut to create a new worksheet from the MASTER worksheet. Opens a dialog box for the Tab name.
Dim sName As String
Dim wks As Worksheet
Worksheets("Master").Copy After:=Worksheets("Master")
Set wks = ActiveSheet
Do While sName <> wks.Name
sName = Application.InputBox _
(Prompt:="Enter new worksheet name")
On Error Resume Next
wks.Name = sName
Range("C1").Value = sName
On Error GoTo 0
Loop
Set wks = Nothing
Call SortWorksheets
End Sub
--------------------------------------------------------
Sub SortWorksheets() 'I got this macro from an answer in this forum from 2004!
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 = 5
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