How to lock a sheet position for always visible in Excel Workbook?
In Excel, when working with a large workbook containing many sheets, the sheet tab bar can quickly become crowded. As you scroll through the tab list—especially if there are more sheets than fit on the screen—the tabs at either end, including the first sheet, may slip out of view. This can be challenging if you rely on a “master” or summary sheet for quick reference and need it to remain accessible at all times.
This article outlines practical ways to ensure your chosen master sheet remains readily visible in your Excel workbook, regardless of which sheet you navigate to or how many sheets are present. These solutions have great value when you need to continually reference instructions, dashboards, or summary data without hunting through sheet tabs.
 |  |  |
Lock sheet position with VBA
Currently, there is no built-in setting in Excel that directly locks a specific sheet’s tab in place for permanent visibility. However, using VBA (Visual Basic for Applications), you can achieve similar results and improve navigation efficiency. Below are two diverse VBA techniques addressing common goals:
Jump to specific sheet using a shortcut
When working through multiple sections or sheets, setting up a shortcut to jump instantly to your master sheet saves time and avoids scrolling through tabs. This method is especially applicable when you often need to check summary data or make entries in a particular “master” sheet on the fly.
1. Press Alt + F11 to open the Microsoft Visual Basic for Applications window.
If you don't see the Developer tab, you may need to enable it through Excel options first.
2. Click Insert > Module to add a new module. Then copy and paste the following VBA code into the blank script area:
VBA: Jump to the master sheet
Sub GoToSheet()
'UpdatebyExtendoffice20180427
Sheets("Master").Activate
End SubNote: In the VBA code line Sheets("Master").Activate, replace "Master" with the name of your target sheet if needed. Always ensure the sheet name matches exactly, as misspellings or extra spaces will result in runtime errors.
3. Save your VBA project, return to Excel, and go to Developer > Macros.
4. In the Macro dialog box, locate the macro you just created. Click Options to set a custom keyboard shortcut for this macro.
5. Confirm your shortcut and close the Macro dialog.
Now you can press your chosen shortcut at any time to jump directly to the master sheet, regardless of your current location in the workbook. This helps maintain focus in busy workbooks and reduces wasted time searching for the main sheet.
Tip: If you continue adding new sheets or rearrange your workbook, this shortcut remains effective as long as the target sheet name does not change. Be aware, if you rename or delete the master sheet, the macro will trigger an error until updated.
Keep a specific sheet always in the front
Some workflows benefit from keeping a particular sheet at the left-most (front) position in the tab list. The following VBA code automatically moves your chosen master sheet to the first position every time a different sheet is activated. This is useful for shared workbooks or dashboards where the summary sheet should always be prominent for all users.
1. Press Alt + F11 to open the Microsoft Visual Basic for Applications window.
2. In the Project-VBAProject pane on the left, double-click ThisWorkbook to open its code window. Paste the code below directly into the sheet:
VBA: Keep master sheet always in the front
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'UpdatebyExtendoffice2018027
Dim xSheet As Worksheet
Application.EnableEvents = False
Set xSheet = Sheets("master")
If Sh.Name <> xSheet.Name Then
Sh.Move , xSheet
xSheet.Activate
Sh.Activate
End If
Application.EnableEvents = True
End Sub
3. Save your work and close the VBA editor. From now on, whenever you click on or activate any sheet, your master sheet will automatically be repositioned to the first tab spot, ensuring constant visibility.
 | |  |
Note: In the VBA code above, "master" refers to the sheet you wish to keep visible at all times. Adjust this name as necessary for your workbook—but always double-check for spelling and case matching. If the named sheet does not exist, the code will not work, so verify before use.
Precautions: If your workbook contains protected or very hidden sheets, moving sheets programmatically may trigger errors. Additionally, this code execution will happen every time a sheet is selected, so for extremely large workbooks, slight lag may occur.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in