Automatic open .. in Excel

[blOkkie]

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.

 

[onlyadrafter]

Hello,

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

then in the THIS WORKBOOK code window copy this code

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

change refs as required.

 

[blOkkie]

this workbook?!

 

[onlyadrafter]

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]

Hello,

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

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)

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

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

 

[blOkkie]

Allright, this was very helpfull!

Thankz alot! It work's now!