Excel 2010, shared workbook does not open to last tab worked in.

[haydenmike]

We have a schedule that about 7 people access. When a user opens the workbook it keeps defaulting to a tab that was the work schedule from several weeks earlier. I would like it to default to opening in the current weekly schedule. Or just open in the last used / saved tab?

 

[mrshl9898]

What is the name of the tab for this week for example?

 

[haydenmike]

This weeks is Nov 19 to Nov 25,18
Next week is Nov 26 to Dec 2,18
and so on

 

[mrshl9898]

Try this, it will need to be entered in "ThisWorkbook" in the VBA editor.

Private Sub Workbook_Open()


weekstartmonth = WorksheetFunction.Text(Now, "Mmm")
weekstartday = Format(Now - Weekday(Now, vbUseSystem) + 1, "DD")
weekendmonth = WorksheetFunction.Text(Now - Weekday(Now, 2) + 8, "Mmm")
weekendday = Format(Now - Weekday(Now, vbUseSystem) + 7, "DD")
myyear = Year(Now)


shtstr = weekstartmonth & " " & weekstartday & " to " & weekendmonth & " " & weekendday & "," & myyear


Sheets(shtstr).Select


End Sub

 

[haydenmike]

I will try this on Monday. Thank you

Try this, it will need to be entered in "ThisWorkbook" in the VBA editor.

Private Sub Workbook_Open()


weekstartmonth = WorksheetFunction.Text(Now, "Mmm")
weekstartday = Format(Now - Weekday(Now, vbUseSystem) + 1, "DD")
weekendmonth = WorksheetFunction.Text(Now - Weekday(Now, 2) + 8, "Mmm")
weekendday = Format(Now - Weekday(Now, vbUseSystem) + 7, "DD")
myyear = Year(Now)


shtstr = weekstartmonth & " " & weekstartday & " to " & weekendmonth & " " & weekendday & "," & myyear


Sheets(shtstr).Select


End Sub