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
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
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.
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
Did you put the code in the ThisWorkbook module?
run-time error ‘9’:
subscript out of range
Gosh..more testing
k…works fine
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.
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.
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
Else
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
Excellent, thank you so much!
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
Sounds possible but can you give a more specific example, such as what should happen on Sundays, Mondays, Wednesdays, Thursdays, and Saturdays.
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).
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.
Thank you for your help. I figured out the answer on my own.
Thanks so much for your help