Tom’s Tutorials For Excel: Open a Workbook To a Specific Sheet and Cell

Tom’s Tutorials For Excel: Open a Workbook To a Specific Sheet and Cell

How can you open a workbook to a specific worksheet and cell? For example, regardless of where you were in the workbook when it was last closed, if you always want the workbook to open by taking you to cell A1 of Sheet3, here’s how.

In VBA terms, the code to accomplish this is a workbook-level event, meaning the code will be placed into the “ThisWorkbook” module of your workbook.

To find your workbook module for Excel versions 2003 or before:
Find the small Excel workbook icon near the upper left corner of your workbook window, usually just to the immediate left of the File menu option. Right-click on that icon, and left-click to select the View Code menu item.

If you are using Excel version 2007 or 2010:
From your worksheet press Alt+F11, then press Ctrl+R.
Find your workbook name in the “Project – VBAProject” left vertical pane (it will be in bold font, looking like VBAProject (YourWorkbookName.xlsx). Expand the Microsoft Excel Object folder for your workbook, right-click on ThisWorkbook and left-click to select View Code.

For any version of Excel, paste this code into the large white area that is your workbook module.
After that, return to your worksheet by pressing Alt+Q and save your workbook.

Private Sub Workbook_Open()
Application.Goto Worksheets("Sheet3").Range("A1")
End Sub

If you want to go to, say, cell CD1025 on Sheet3, and ensure that cell CD1025 is always in the top left corner of your worksheet when you open the workbook, set the GoTo argument for Scroll to True, as in this example:

Private Sub Workbook_Open()
Application.Goto Worksheets("Sheet3").Range("CD1025"), True
End Sub
Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
8 comments on “Tom’s Tutorials For Excel: Open a Workbook To a Specific Sheet and Cell
  1. Marty Taylor says:

    I added barcode to multiple tabs of excel spreadsheet. Now no matter where I save the file, it opens the tabs to the lower part of the spreadsheet, below the UPC. Is there anything I can do?

    Thank YOU

    • Tom Urtis says:

      Could be due to a number of things. Did you set Freeze Panes on your worksheet. Is there any programming code in the file relating to that worksheet. Do the bar codes have hyperlinks to them that force navigation to a certain part of the worksheet.

  2. Hassan Raza says:

    when i set the specific view in sheets so that it may open from that specific cell# and specific sheet,after that when i enter data in that sheet and save and reopen sheet it didnt open from that specific coloumn? what can i do

  3. Bob says:

    run-time error ‘9’:
    subscript out of range

  4. Bob says:

    Gosh..more testing

  5. Bob says:

    k…works fine

    • Tom Urtis says:

      I assume by this comment you are good to go. The code is a one liner although it might look like 2 lines if you’re seeing it on a phone. Also it depends on there being a Sheet3.

Leave a Reply

Your email address will not be published. Required fields are marked *