Controlling Worksheet Tab Position

[prcrstntr]

The Worksheet names that I have make the tabs wide enough that five tabs show at any given time when scrolling through the twelve Worksheets in my Workbook. I would like one of the Worksheet tabs to be seen at all times, preferably the right most of the five tabs that can be seen. Essentially, the same as Freezing a Pane in a Worksheet, but Freezing a Worksheet Tab instead. Is this possible?

 

[Yongle]

Can achieve with VBA like this..

Below assumes that none of your worksheets are hidden and that the structure of the workbook is not protected
I would not expect standard sheet protection to cause any issues

Create a copy of your workbook and test on that

Place code below in ThisWorkbook Module and amend Master (to name of frozen 5th sheet) BEFORE exiting VBA
{ALT}{F11} to go to VBA window \ double-click on ThisWorbook in VBA Project Window \ Paste code in the window that opens up

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If ThisWorkbook.Worksheets.Count < 6 Then Exit Sub
    Dim c As Long, s As Long, wsM As Worksheet
    On Error Resume Next
    Set wsM = Sheets("[I][COLOR=#ff0000]Master[/COLOR][/I]")
    On Error GoTo Handling
    
    Application.EnableEvents = False
    
    c = Worksheets.Count
    If wsM.Index <> c Then wsM.Move after:=Worksheets(c)
    wsM.Activate
    s = Sh.Index
    
    Select Case s
       Case Is < 5
           Sheets(1).Activate
           wsM.Move after:=Sheets(4)
       Case Is < (c - 4)
           Sheets(s - 3).Activate
           wsM.Move after:=Sheets(s)
    End Select
    Sh.Activate
Handling:
    Application.EnableEvents = True
End Sub

Run Events to re-enable event trigger if inadvertently disabled during testing!!
Place code below in a standard Module
(when in VBA window) Inserts a Module with {ALT} I M \ Paste code in the window that opens up

Sub Events()
    Application.EnableEvents = True
End Sub

Save the workbook as macro enabled

 

[footoo]

It should be noted that the procedure in post # 2 clears the clipboard - so copy/paste from one sheet to another will not be possible.

If this is unacceptable, the code could run from a normal macro (suggest via a shortcut key) as and when necessary (i.e. when "Master" sheet is not visible).

 

[Yongle]

@footoo makes a valid point

What you are requesting is a bit messy - but it was fun finding a way to achieve it

Presumably you are forever referring to the 5th sheet
In which case try a simple toggle attached to a shortcut

Place in a new standard module and amend sheet name

Option Explicit
Public OldSheet As Worksheet

Sub ToggleMaster()
Const sh = "[COLOR=#ff0000]Master[/COLOR]"
    If Not ActiveSheet.Name = sh Then
        Set OldSheet = ActiveSheet
        Sheets(sh).Activate
    Else
        If Not OldSheet Is Nothing Then OldSheet.Activate
    End If
End Sub

- allows you to toggle between the 2 sheets