Automatic open .. in Excel

blOkkie

New Member
Joined
Oct 4, 2006
Messages
3
I made an xls file and i've got many tabs .. for every week 1 tab... (week 1 week 2 week 3 etc etc)

I would like excel to open automaticly in the week it is now...


so now it is week 40... i want it automaticly to open in week 40...

next week i want it to open in week 41.. etc...etc...

does someone know how I can fix this?!

Thankz in advance.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

In a cell somewhere enter =Weeknum(today())

then in the THIS WORKBOOK code window copy this code

Code:
Private Sub Workbook_Open()
Sheets("Week " & Sheets("sheet1").Range("D3").Value).Activate
End Sub

change refs as required.
 

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

I assume you know how to put the code in a standard module.

When you are in the VB window, you may have a window similar to Windows Explorer, if not select VIEW --> PROJECT EXPLORER, in here, it will have a list of the spreadsheets you have open

e.g. VBAProject(Book1)

click the + next to this to show a -

you should see Microsoft Excel Objects, and a list of the TAB names, e.g. Sheet1, Sheet2 etc, & This Workbook

double click on the This workbook, the main window will change and show (General) and (Declarations) at the top. THis is where the code needs to go.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hello,

this works for me - without using a "helper"cell

Code:
Private Sub Workbook_Open()
Dim weekNum As Integer
weekNum = Int((Date - DateSerial(Year(Date), 1, 1)) / 7) + 1
Sheets("week " & weekNum).Select
End Sub
perhaps add some errorhandling - you never know - in case a sheet is deleted or not created yet ?
(as an extra example used some slightly other code to select the sheet)
Code:
Private Sub Workbook_Open()
Dim shName As String

On Error Resume Next
shName = "week " & Int((Date - DateSerial(Year(Date), 1, 1)) / 7) + 1
Sheets(shName).Select

    If Err Then
    Err.Clear
    MsgBox "sheet """ & shName & """ doesn't exist", 48, "ERROR"
    End If

End Sub
Code:
Private Sub Workbook_Open()
'your code
End Sub
TO INSTALL IN THISWORKBOOK CODE WINDOW:
1. Rightclick the little Excel-icon on the topleft of your page just beside the Filemenu
2. Select "View Code" in drop down menu
3. VBE window will open ... paste code in and exit VBE


kind regards,
Erik
 

Forum statistics

Threads
1,136,650
Messages
5,676,996
Members
419,667
Latest member
MegEri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top