Focus on new Worksheet after sorting

RaeB

New Member
Joined
Dec 4, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
Try adding a line to your original code that selects the sheet after the sort is run, i.e.
Rich (BB code):
...
Call SortWorksheets
wks.Activate

End Sub
 

RaeB

New Member
Joined
Dec 4, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Along with Joe's suggested in post#2 you will need to delete this line of your code
VBA Code:
Set wks = Nothing
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
Along with Joe's suggested in post#2 you will need to delete this line of your code
VBA Code:
Set wks = Nothing
Good catch!
Adding my line of code won't work without doing that.
 
Solution

RaeB

New Member
Joined
Dec 4, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad we were able to help!
:)
 

Watch MrExcel Video

Forum statistics

Threads
1,126,952
Messages
5,621,798
Members
415,856
Latest member
jimb2k

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top