Opening a Worksheet Dependant on Date

[Danny]

I am creating a work management schedule for my staff and
i want it to be really easy to use and menu driven. I have
each week of their work as a separate worksheet numbered
WK1 - WK52 for the year. I want to know what the code
would be which would automatically select the relevant
worksheet depending on the date, i.e if it is the 1st of
January the macro opens WK1 and if it is the 30th of
December the macro opens WK52.

I.e.

if today = 01 Jan, 02 Jan, 03 Jan, 04 Jan, 05 Jan, 06 Jan,
or 07 Jan open 'WK1!'A1

etc.

Anyone know how i may do this?

 

[Tom Ogilvy]

Jan 01 could start on any day of the week - your weeks are just 7 day
increments starting from Jan 01 without regard to the day of the week?

 

[Guest]

Use a lookup table in a new sheet with 2 columns, the date
of the beginning of the week and the week number. Access
with Now function to get the week number to allow you to
select the appropriate sheet

 

[Rabbit]

'You could try pasting this into VBA module on your spreadsheet,
'save it and then hopefully when you open the spreadsheet it'll
'select the relevant sheet, as long as they're named WK1, WK2 etc.

Sub Autpen()
AccessThisWeeksWork
End Sub

Sub AccessThisWeeksWork()
Dim TheDate As Date
TheDate = Now '
CurrentWeeksWorksheet = "WK" & DatePart("ww", TheDate)
Sheets(CurrentWeeksWorksheet).Activate
End Sub

'If you look at the Excel VBA help under "DatePart" you can set which day
'of the week is the first day & you can also set which week of the year
'is the first week.

'Rabbit