Focus on new Worksheet after sorting

[RaeB]

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

 

[Joe4]

Try adding a line to your original code that selects the sheet after the sort is run, i.e.

...
Call SortWorksheets
wks.Activate

End Sub

 

[RaeB]

Thank you for your reply, but that did not work. It's like the Sort function did not happen and the new worksheet stayed right were it was created.

 

[Joe4]

Was it working before you added that line in there?
If it was, and if you added that line of correct place, it should not do anything to prevent the sort from happening (as the code you added was AFTER the sort call).

 

[Fluff]

Along with Joe's suggested in post#2 you will need to delete this line of your code

Set wks = Nothing

 

[Joe4]

Along with Joe's suggested in post#2 you will need to delete this line of your code

Set wks = Nothing

Good catch!
Adding my line of code won't work without doing that.

 

[RaeB]

Thank you. I could not get it to work last week so I started working on other projects. Came back to it today and it works fine. Thank you everyone for your help.

 

[Joe4]

You are welcome.
Glad we were able to help!