Open to today's date

Hello

I have a workbook with a sheet for each week of the year. The dates are in
the same row in each sheet. I would like to be able to have the workbook
open to the sheet that contains today's date.

Thanks for your help!

Rick
Fri May 09 12:35:44 CDT 2008

How are the worksheets named? Which "same row" are the dates in? Which
cells? The more information you give us about your set up, the better able
we are to figure out what to tell you; otherwise, we are reduced to pure
guessing and those guesses usually turn out to be wrong.

Rick


"KathyN" <KathyN@discussions.microsoft.com> wrote in message
news:9B1312F1-A5EF-4A9F-B7D1-6232064A5FBC@microsoft.com...
> Hello
>
> I have a workbook with a sheet for each week of the year. The dates are
> in
> the same row in each sheet. I would like to be able to have the workbook
> open to the sheet that contains today's date.
>
> Thanks for your help!

Joel
Fri May 09 12:56:09 CDT 2008

This code will open the correct sheet for the current week. You need to
modify the code to match your sheet names. The code uses Sunday as the 1st
day of the week. The 1st week of the year can have less than 7 days if the
year doesn't start on a Sunday.

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)

Jan1 = DateSerial(Year(Date), 1, 1)
DayofWeek = Weekday(Jan1)
FirstSunday = Jan1 - DayofWeek + 1 'Sunday may be inprevious year

Weeknumber = Int((Date - FirstSunday) / 7)
SheetName = "Week_" '<=modify to match your sheet names
Worksheets(SheetName & Weeknumber).Activate
End Sub

"KathyN" wrote:

> Hello
>
> I have a workbook with a sheet for each week of the year. The dates are in
> the same row in each sheet. I would like to be able to have the workbook
> open to the sheet that contains today's date.
>
> Thanks for your help!

KathyN
Fri May 09 14:52:02 CDT 2008

Rick,

The worksheets are named 'WE m/d' corresponding to each Friday, i.e. WE 5/9.
The dates are in cells B3:G3 in each sheet, and are formatted m/dd/yyy.

Thanks

"Rick Rothstein (MVP - VB)" wrote:

> How are the worksheets named? Which "same row" are the dates in? Which
> cells? The more information you give us about your set up, the better able
> we are to figure out what to tell you; otherwise, we are reduced to pure
> guessing and those guesses usually turn out to be wrong.
>
> Rick
>
>
> "KathyN" <KathyN@discussions.microsoft.com> wrote in message
> news:9B1312F1-A5EF-4A9F-B7D1-6232064A5FBC@microsoft.com...
> > Hello
> >
> > I have a workbook with a sheet for each week of the year. The dates are
> > in
> > the same row in each sheet. I would like to be able to have the workbook
> > open to the sheet that contains today's date.
> >
> > Thanks for your help!
>
>

Rick
Fri May 09 15:03:09 CDT 2008

How are you getting the forward slash into the worksheet's name (as far as I
know, it is an invalid character for use there)?

Rick


"KathyN" <KathyN@discussions.microsoft.com> wrote in message
news:4213AA89-8573-47D0-A834-EE8BF38BC3CD@microsoft.com...
> Rick,
>
> The worksheets are named 'WE m/d' corresponding to each Friday, i.e. WE
> 5/9.
> The dates are in cells B3:G3 in each sheet, and are formatted m/dd/yyy.
>
> Thanks
>
> "Rick Rothstein (MVP - VB)" wrote:
>
>> How are the worksheets named? Which "same row" are the dates in? Which
>> cells? The more information you give us about your set up, the better
>> able
>> we are to figure out what to tell you; otherwise, we are reduced to pure
>> guessing and those guesses usually turn out to be wrong.
>>
>> Rick
>>
>>
>> "KathyN" <KathyN@discussions.microsoft.com> wrote in message
>> news:9B1312F1-A5EF-4A9F-B7D1-6232064A5FBC@microsoft.com...
>> > Hello
>> >
>> > I have a workbook with a sheet for each week of the year. The dates
>> > are
>> > in
>> > the same row in each sheet. I would like to be able to have the
>> > workbook
>> > open to the sheet that contains today's date.
>> >
>> > Thanks for your help!
>>
>>

KathyN
Fri May 09 15:11:01 CDT 2008

You are right - it's a dash, 5-9

"Rick Rothstein (MVP - VB)" wrote:

> How are you getting the forward slash into the worksheet's name (as far as I
> know, it is an invalid character for use there)?
>
> Rick
>
>
> "KathyN" <KathyN@discussions.microsoft.com> wrote in message
> news:4213AA89-8573-47D0-A834-EE8BF38BC3CD@microsoft.com...
> > Rick,
> >
> > The worksheets are named 'WE m/d' corresponding to each Friday, i.e. WE
> > 5/9.
> > The dates are in cells B3:G3 in each sheet, and are formatted m/dd/yyy.
> >
> > Thanks
> >
> > "Rick Rothstein (MVP - VB)" wrote:
> >
> >> How are the worksheets named? Which "same row" are the dates in? Which
> >> cells? The more information you give us about your set up, the better
> >> able
> >> we are to figure out what to tell you; otherwise, we are reduced to pure
> >> guessing and those guesses usually turn out to be wrong.
> >>
> >> Rick
> >>
> >>
> >> "KathyN" <KathyN@discussions.microsoft.com> wrote in message
> >> news:9B1312F1-A5EF-4A9F-B7D1-6232064A5FBC@microsoft.com...
> >> > Hello
> >> >
> >> > I have a workbook with a sheet for each week of the year. The dates
> >> > are
> >> > in
> >> > the same row in each sheet. I would like to be able to have the
> >> > workbook
> >> > open to the sheet that contains today's date.
> >> >
> >> > Thanks for your help!
> >>
> >>
>
>

Joel
Fri May 09 15:12:00 CDT 2008

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)

DayofWeek = Weekday(Date)

'6 is the Friday Day considering Sunday as 1
FridayDate = Date + (6 - DayofWeek)

SheetName = "WE " & Month(FridayDate) & "/" & Day(FridayDate)
Worksheets(SheetName & Weeknumber).Activate
Range("B3").Offset(0, DayofWeek - 1).Activate
End Sub

"KathyN" wrote:

> Rick,
>
> The worksheets are named 'WE m/d' corresponding to each Friday, i.e. WE 5/9.
> The dates are in cells B3:G3 in each sheet, and are formatted m/dd/yyy.
>
> Thanks
>
> "Rick Rothstein (MVP - VB)" wrote:
>
> > How are the worksheets named? Which "same row" are the dates in? Which
> > cells? The more information you give us about your set up, the better able
> > we are to figure out what to tell you; otherwise, we are reduced to pure
> > guessing and those guesses usually turn out to be wrong.
> >
> > Rick
> >
> >
> > "KathyN" <KathyN@discussions.microsoft.com> wrote in message
> > news:9B1312F1-A5EF-4A9F-B7D1-6232064A5FBC@microsoft.com...
> > > Hello
> > >
> > > I have a workbook with a sheet for each week of the year. The dates are
> > > in
> > > the same row in each sheet. I would like to be able to have the workbook
> > > open to the sheet that contains today's date.
> > >
> > > Thanks for your help!
> >
> >

Rick
Fri May 09 15:43:26 CDT 2008

This Workbook_Open event code should do what you want...

Private Sub Workbook_Open()
Dim SH As Worksheet
For Each SH In Worksheets
If "WE " & Format(Now + 6 - Weekday(Now) - _
7 * (Weekday(Now) = 7), "m-d") = SH.Name Then
SH.Select
Exit For
End If
Next
End Sub

Rick


"KathyN" <KathyN@discussions.microsoft.com> wrote in message
news:307C1EFF-C73F-4A72-B154-1197225BFF09@microsoft.com...
> You are right - it's a dash, 5-9
>
> "Rick Rothstein (MVP - VB)" wrote:
>
>> How are you getting the forward slash into the worksheet's name (as far
>> as I
>> know, it is an invalid character for use there)?
>>
>> Rick
>>
>>
>> "KathyN" <KathyN@discussions.microsoft.com> wrote in message
>> news:4213AA89-8573-47D0-A834-EE8BF38BC3CD@microsoft.com...
>> > Rick,
>> >
>> > The worksheets are named 'WE m/d' corresponding to each Friday, i.e. WE
>> > 5/9.
>> > The dates are in cells B3:G3 in each sheet, and are formatted m/dd/yyy.
>> >
>> > Thanks
>> >
>> > "Rick Rothstein (MVP - VB)" wrote:
>> >
>> >> How are the worksheets named? Which "same row" are the dates in? Which
>> >> cells? The more information you give us about your set up, the better
>> >> able
>> >> we are to figure out what to tell you; otherwise, we are reduced to
>> >> pure
>> >> guessing and those guesses usually turn out to be wrong.
>> >>
>> >> Rick
>> >>
>> >>
>> >> "KathyN" <KathyN@discussions.microsoft.com> wrote in message
>> >> news:9B1312F1-A5EF-4A9F-B7D1-6232064A5FBC@microsoft.com...
>> >> > Hello
>> >> >
>> >> > I have a workbook with a sheet for each week of the year. The dates
>> >> > are
>> >> > in
>> >> > the same row in each sheet. I would like to be able to have the
>> >> > workbook
>> >> > open to the sheet that contains today's date.
>> >> >
>> >> > Thanks for your help!
>> >>
>> >>
>>
>>