Fix specific Tab's Position on Ribbon

[vikrampnz]

Hello

I have a workbook with more than 100 worksheets and the number is growing as I add a new worksheet for every new client. I have 3 worksheets (viz. "Summary" , "Original Template" and "Reports") that I need to access all the time and I am wondering if there is a way these 3 worksheets are always shown as first 3 tabs (from Left) on the tabs ribbon. I have a code in place to alphabetically arrange the worksheet tabs, but I want these three worksheet tabs to be excluded from the alphabetical order.

Below is the code I am using to alphabetically organise the tabs:

Sub SortWorksheetsTabs()

With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .DisplayStatusBar = False
        .EnableEvents = False
    End With

Dim ShCount As Integer, i As Integer, j As Integer
ShCount = Sheets.Count

For i = 1 To ShCount - 1
    For j = i + 1 To ShCount
        If UCase(Sheets(j).Name) < UCase(Sheets(i).Name) Then
            Sheets(j).Move Before:=Sheets(i)
        End If
    Next j
Next i

 With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
        .DisplayStatusBar = True
        .EnableEvents = True
    End With
End Sub

Could anyone please help me with this ?

Thanks

V

 

[Snakehips]

@vikrampnz if you start with the 3 sheets at left and always add a new sheet to the right of them then a simple change of the initial value of i will do it.

Sub SortWorksheetsTabs()

With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .DisplayStatusBar = False
        .EnableEvents = False
    End With

Dim ShCount As Integer, i As Integer, j As Integer
ShCount = Sheets.Count

For i = 4 To ShCount - 1 '<<<<<<<
    For j = i + 1 To ShCount
        If UCase(Sheets(j).Name) < UCase(Sheets(i).Name) Then
            Sheets(j).Move Before:=Sheets(i)
        End If
    Next j
Next i

 With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
        .DisplayStatusBar = True
        .EnableEvents = True
    End With
End Sub

Hope that helps.

 

[vikrampnz]

Hello Snakehips
Thanks for the quick response. I tried that modification in the code, but unfortunately its not working. It still creates a copy of the new sheet at the very left of the tabs ribbon. I am using "move or copy" option on the worksheet "Original Template", to create more sheets with the same templates and then I rename them with new Client name.
Also, when it alphabetically sorts the sheet tabs, its also sorting the first 3 tabs and shifting them to their respective alphabetical positions.

 

[Snakehips]

When you create copies of the 'Original Template', you get an option to select which sheet you want it to go before. If it is always the left-most then that is because you are not making a selection from the list. If you select the fourth sheet down, below your required three leftmost, then the new sheet will always be fourth from left.
I cannot see how the revised code I supplied will sort the left-most three sheets.?

 

[vikrampnz]

Hello Snakehips... you're right....the first three sheets tabs are not moving. Mine previously moved because the new sheet went to extreme left and the third sheet became fourth. This is serving my purpose. Just wondering, if there's way to automate this process of selecting the 4th sheet down when selecting the "copy next to" option as there will be multiple people using this sheet and I am trying to eliminate the possibilities of human errors and trying to make it as user friendly as possible, so people use it without hesitation.

I do appreciate your time and help in this !!

Cheers

 

[Snakehips]

V, does this help?

Sub SortWorksheetsTabs()


'CODE TO COPY AND NAME NEW SHEET 4
' Either include here with the sort code or cut it out to be a sepeaate macro

Sheets("Original Template").Copy Before:=Sheets(4)
BadName:
On Error GoTo BadName
    NewName = InputBox("Please a valid sheet name", "NewSheet")
    Sheets(4).Name = NewName
On Error GoTo 0

'END OF THE COPY AND NAME CODE


With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .DisplayStatusBar = False
        .EnableEvents = False
    End With

Dim ShCount As Integer, i As Integer, J As Integer
ShCount = Sheets.Count

For i = 4 To ShCount - 1
    For J = i + 1 To ShCount
        If UCase(Sheets(J).Name) < UCase(Sheets(i).Name) Then
            Sheets(J).Move Before:=Sheets(i)
        End If
    Next J
Next i

 With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
        .DisplayStatusBar = True
        .EnableEvents = True
    End With
End Sub

 

[vikrampnz]

Hello Snakehips

The modified code is working well. However, I am having issues getting to run it automatically when trying to create a copy of the sheet "Original Template". I had the code in a Module earlier hich didint work, then I tried to copy the code under "This Workbook" and also "Original Template". When I run the code manually, it works perfectly.

How can I activate the code automatically when I right click on the worksheet "Original template" and select "Copy or Move" option?

Cheers

 

[Snakehips]

Only you know what will suit your circumstances. However you will not trigger the code by using the standard clicks to get to sheet Copy or Move. And why would you? The code is designed to copy 'Original Template' automatically and ensure that you don't mess the first three sheets. Look to use a shape or button in the sheet and asign the macro to that? Or maybe a keyboard shortcut? How were you running the original code?

 

[vikrampnz]

Hello Snakehips

I linked the code to a Command button that I put in the sheet "Original Template". So, it works beautifully now. Now, when I create a new worksheet by inserting the name in the Pop up window, it creates that sheet perfectly and aligns it alphabetically in the list of worksheets on the ribbon, but it opens the sheet previous to the newly created worksheet i.e. On the ribbon, the sheet on the left of the newly created sheet. Since I have in excess of 100 + sheets, I am having to scroll quite a bit to find the newly created sheet and open it for further work.

Is there a way, on new sheet creation excel can open the newly created sheet instead of opening the one prior to it?

Thanks
V

 

[Snakehips]

Just edit the very end of the code to be as below.

'activate new sheet
    Sheets(NewName).Activate
    
End Sub