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: , , , , , , , , , , , ,
17 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.

  6. Bryan says:

    Is there a way to use this same code, but go to a different cell each time based on current date? ie: Column A has all the dates listed, and when I open the workbook, I want it to open to the cell in column B next to the current date.

    • Tom Urtis says:

      You said nothing about which specific worksheet is to be searched, so this is a catch-all from Hans Herber.

      Worksheets are searched in their column A for today’s date, and if found, that sheet and cell is activated.

      A special feature of this example is that if the current date is a Saturday or Sunday, the following Monday date is located, to simulate consideration for working days only.

      Sub FindWorkDay()
      Dim wks As Worksheet
      Dim dat As Date
      Dim vRow As Variant
      dat = Date
      Call MyWorkDay(dat)
      For Each wks In Worksheets
      vRow = Application.Match(CDbl(dat), wks.Columns(1), 0)
      If Not IsError(vRow) Then Exit For
      Next wks
      If Not IsError(vRow) Then
      Application.Goto wks.Cells(vRow, 2), True
      MsgBox "Date was not found!"
      End If
      End Sub

      Sub MyWorkDay(ByRef dat As Date)
      Dim iDay As Integer
      iDay = WorksheetFunction.Weekday(dat)
      Select Case iDay
      Case 7: dat = dat + 2
      Case 1: dat = dat + 1
      End Select
      End Sub

  7. Bryan says:

    Excellent, thank you so much!

  8. Cari says:

    Is there a way to use this same code, but go to a different tab each time based on current date? ie: I have a tab for each Tuesday and Friday during the month. I would like it to automatically open to the correct tab based on the date

  9. Cari says:

    Ideally, Saturdays, Sundays and Mondays would revert back to the Friday before and Wednesday and Thursday would open to the Tuesday before. So if I were to open it up today (10/28) I would like it to open to the tab dated (10/27).

    • Tom Urtis says:

      I can tell there is more to this than meets the eye. For example, you wrote…
      “would like it to open to the tab dated (10/27)”
      …which raises several questions that I do not want to guess at.
      For example, you really do not (because Excel won’t let you) enter a backslash character in a worksheet tab name. So you need to get specific about exactly what is happening with the sheet names, if there is a sheet name for every day (every day means every day, all 366 days in this calendar leap year and 365 the other years. Basically keep in midn that no one except you has seen your workbook so unless you get precise with the workbook setup, you cannot get a reply you can use.

  10. Cari says:

    Thank you for your help. I figured out the answer on my own.

  11. Fatima says:

    Thanks so much for your help

Leave a Reply

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