Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Freezing Worksheet Tabs.

Freezing Worksheet Tabs

by Allen Wyatt
(last updated May 16, 2015)

5

Jonathan has a workbook that contains over fifty worksheets, one of which is named "Main" and is positioned as the first tab in the workbook. He is constantly having to revert back to the "Main" worksheet. In order to display the worksheet he must either click back a tab at a time or scroll all the way to the left of the tabs (by clicking on the control at the far left of the tabs) and then select the "Main" tab. This last method is the easiest, but still is time consuming. Jonathan wonders if there is a way, much like freezing a pane, to freeze a worksheet tab. He would like the "Main" tab to always be visible, and the tabs to its right to scroll.

The short answer is no, there is not a way in Excel to freeze the worksheet tabs. That being said, there are several things you can do to get the results you want.

One possible solution is to use hyperlinks in your worksheets. Many people set up a system where their main worksheet functions as a table of contents to the other worksheets in the workbook. Each worksheet is hyperlinked from the main worksheet, and each non-main worksheet has a hyperlink back to the main worksheet. Thus they can navigate very quickly between the main and secondary worksheets just by clicking the hyperlinks.

Another option is to remember that you can right-click on the worksheet tab controls at the left of the tabs at the bottom of the Excel window. When you do, you get a list of the first fifteen worksheet names, and you can easily select the "Main" worksheet.

Still another option is to set up a very simple macro that always displays the "Main" worksheet:

Sub GoToMain()
    Sheets("Main").Select
End Sub

You can assign this macro to either a shortcut key or a toolbar button so that you could use it very quickly. When run, the worksheet named "Main" is always displayed.

If you absolutely want to always have the "Main" sheet visible in the tabs area, then you must resort to a macro that will continuously reorder the tabs so that "Main" is always visible.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim sc As Long ' count of sheets
    Dim NewPos As Long ' index of serlected sheet

    Application.EnableEvents = False
    Application.ScreenUpdating = False

    If ActiveSheet.Index <> 1 Then
        sc = Sheets.Count
        NewPos = ActiveSheet.Index
        For i = 2 To NewPos - 1
            Sheets(2).Move After:=Sheets(sc)
        Next i
        Sheets(1).Activate
        Sheets(2).Activate
    End If

    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

This macro needs to be part of the ThisWorkbook object, so make sure you add it into the proper place in the VBA Editor. It always moves the worksheets in positions 2 through however many sheets you have so that the desired worksheet is in the second position. This means that the worksheet in the first position (Main) never moves.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3178) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Freezing Worksheet Tabs.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Exporting Latitude and Longitude

A handy way to store latitude and longitude values in Excel is to treat them as regular time values. When it comes around to ...

Discover More

Showing Visited Hyperlinks

Many people like to use Excel to keep track of lists of hyperlinks. Want to keep a permanent record of which hyperlinks from ...

Discover More

Controlling the Format of Cross-References

When you use fields to add cross-references to tables or figures, Word normally takes care of formatting the words used in ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

MORE EXCELTIPS (MENU)

Changing the Height of Worksheet Tabs

Do you need your worksheet tabs to be taller than what they are? You can't make the adjustment in Excel, but you can make it ...

Discover More

Getting the Name of the Worksheet Into a Cell

Excel allows you to change the names assigned to the worksheets in a workbook. If you want to have those names appear in a ...

Discover More

Referencing Worksheet Tabs

Ever want to use the name of a worksheet tab within a cell? Here's how you can access that information using the CELL ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 3 - 0?

2016-10-28 15:26:37

dovid lando

Is there any way to ensure that the Main sheet stays as the first sheet? I am always adding sheets, and I dont want to go fishing for the main one?


2016-09-17 13:51:04

Quentin Johnston

Is there a way to make this work when I go to a sheet via hyperlink?


2015-06-15 17:51:50

Heath

Fred Burg! Thats Perfect! Thanks


2015-05-16 09:48:57

Hugo Last

Private Sub Workbook_SheetActivate(ByVal shAsObject)
Application.EnableEvents = False

Dim wb As Workbook

Set wb = ActiveWorkbook
Application.ScreenUpdating = False
If sh.Name <> "Index" Then
wb.Sheets("Index").Move before:=sh
Else
wb.Sheets("Index").Move before:=wb.Sheets(1)
End If

sh.Select

Application.EnableEvents = True

Set wb = Nothing
Application.ScreenUpdating = True
End Sub


2015-05-16 08:40:58

Fred Burg

Another way, perhaps very simple, is to set up a named cell (eg, A1) on the Main sheet. Then from anywhere, click F5 (Goto), double-click on the name you assigned, and you're there.


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing