Freezing Worksheet Tabs

The master? Wow. (Thanks for the compliment.)

Yes, the code does not include that, and it should for completeness. However, the code will still work without it, provided you don't use "Option Explicit" in the module. If you do use it, then you'll get an error that is correctable by declaring the variable as you suggest.

Thanks, Rebecca.

-Allen

While I hate to challenge the master, is it possible that your code above forgot to include: Dim i As Long?

Vince and Elliot suggest opening a second window with "Main" in it, but this is not very useful if the purpose of "Main" is for convenient hyperlinks to the other sheets.
The shortcuts to move from sheet to sheet are Ctrl+PageDown and Ctrl+PageUp.
For related discussion, see https://excelribbon.tips.net/T007094_Jumping_to_a_Specific_Worksheet.html
and https://excelribbon.tips.net/T003591_Picking_Worksheets_Quickly.html

...OR... Just open a second window and keep "Main" in it. Flip to it any time you want.

Hi Allen. Like Jonathan, in my Excel workbooks I have many tabs (worksheets) and I was able to scroll quickly between these worksheets with the cursor on the tabs and swiping either way with two fingers. Now, for no reason this has simply stopped working and I have to scroll across the tabs (worksheets) one click at a time. It is like I have triggered something in Excel . . . but I don’t know what. The two finger touch pad scrolling was very helpful and saved a lot of time as I could scroll quickly either way to any worksheet. No other changes have been made to my touchpad settings in Windows 10. Hope you may have some ideas. Thank you. Regards, Ron

Ctrl-Click the left-arrow in the worksheet selection pane...it will take you to the first worksheet no matter how far you are to the right.
Right-click it and it will show the Activate window.

The proposed macro to be pasted in This Workbook does not work quite well.
It looks like, when we select for example 10th sheet, that it will become first from the left and sheets that were in positions from 1st to 9th will be moved to the end. It debugs for a long time in case of big files.

I suggest below macro for ThisWorkbook option. It simply moves tab "Main" before active sheet.
If your tab is called differently than "Main" it must be changed in the code in line: Worksheets("Main").Move Before:=Sheets(sc)
If you prefer to move tab "Main" to the right side of active sheet instead of the left side simply change "Before" in code to "After".


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim sc As Long ' ActiveSheet

Application.EnableEvents = False
Application.ScreenUpdating = False

sc = ActiveSheet.Index

Worksheets("Main").Move Before:=Sheets(sc)
Sheets(sc).Activate

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

Another solution can be where many of the 50 worksheets in the above example are only used infrequently. If you hide such rarely used worksheets you may be able to leave just the ones you are working with on the tabs row such that Main is always visible. Shortening tab names and the length of the horizontal scrolling bar also will help.

Another option is to create a command button for the macro below:

Sub showsheets()
Application.CommandBars("workbook tabs").ShowPopup
End Sub

Another option is to create a rangename on the Main sheet and use the drop down box in the Name Box (cell reference box to the left of the formula bar) to go to the Main sheet.
The Name has have a scope of workbook (which seems to be the default anyway). And if you are using range names already put an underscore in front of it so that it appears first eg _MAIN

PS: if you then want to go back to your original sheet F5 then enter will get you back there.

Why not simply open a second window? Jonathan could size and position the two windows side-by-side. Then he could always keep one window on "Main" and roam around freely in the other window. Or am I missing something?