Dates to Worksheets

Hello.
I have a spreadsheet that has a list of worksheets that are named certain
dates and I would like to create an event that when I open the file it goes
to that tab according to today's date. It seems fairly easy but I think that
I'm complicating the problem. Thanks for your help.
--
D

You have to remember that the name of the worksheet is a string, but the date
function returns a string...

Sub FindSheet()

Sheets(CStr(Format(Date, "mmm d, yyyy"))).Select
End Sub

Something like that depending on the format of your sheet name... You will
also want to refer to it in your on open event...

HTH

"D" wrote:

> Hello.
> I have a spreadsheet that has a list of worksheets that are named certain
> dates and I would like to create an event that when I open the file it goes
> to that tab according to today's date. It seems fairly easy but I think that
> I'm complicating the problem. Thanks for your help.
> --
> D

Ok, but the tabs that are dates run from now until 12-26-05 and the tabs are
by weeks. So each week is a tab, and so on a day like 2-1-05 that would bring
up the 1-31-05 tab because it is from the week of 1-31-05 to 2-6-05. So that
day would bring up that worksheet. I would think that I would have to do a
loop of some sort or maybe an array? Any ideas? Help please!
Thanks,
D

"Jim Thomlinson" wrote:

> You have to remember that the name of the worksheet is a string, but the date
> function returns a string...
>
> Sub FindSheet()
>
> Sheets(CStr(Format(Date, "mmm d, yyyy"))).Select
> End Sub
>
> Something like that depending on the format of your sheet name... You will
> also want to refer to it in your on open event...
>
> HTH
>
> "D" wrote:
>
> > Hello.
> > I have a spreadsheet that has a list of worksheets that are named certain
> > dates and I would like to create an event that when I open the file it goes
> > to that tab according to today's date. It seems fairly easy but I think that
> > I'm complicating the problem. Thanks for your help.
> > --
> > D

You can probably use formulas found here to determine what the name of your
sheet is

http://www.cpearson.com/excel/DateTimeVBA.htm

Chip Pearson's page on VBA and Dates

--
Regards,
Tom Ogilvy

"D" <valdezdj@fotf.org> wrote in message
news:550EF0BC-6FC3-4257-BE20-5B7BFEC133FF@microsoft.com...
> Ok, but the tabs that are dates run from now until 12-26-05 and the tabs
are
> by weeks. So each week is a tab, and so on a day like 2-1-05 that would
bring
> up the 1-31-05 tab because it is from the week of 1-31-05 to 2-6-05. So
that
> day would bring up that worksheet. I would think that I would have to do a
> loop of some sort or maybe an array? Any ideas? Help please!
> Thanks,
> D
>
> "Jim Thomlinson" wrote:
>
> > You have to remember that the name of the worksheet is a string, but the
date
> > function returns a string...
> >
> > Sub FindSheet()
> >
> > Sheets(CStr(Format(Date, "mmm d, yyyy"))).Select
> > End Sub
> >
> > Something like that depending on the format of your sheet name... You
will
> > also want to refer to it in your on open event...
> >
> > HTH
> >
> > "D" wrote:
> >
> > > Hello.
> > > I have a spreadsheet that has a list of worksheets that are named
certain
> > > dates and I would like to create an event that when I open the file it
goes
> > > to that tab according to today's date. It seems fairly easy but I
think that
> > > I'm complicating the problem. Thanks for your help.
> > > --
> > > D

go to the VBE and go to the Thisworkbook module of the workbook.

in the left dropdown select workbook and in the right, select Open (top of
the module dropdowns).

change the formatting or logic to produce the date related name of the sheet
you want to go to.

Private Sub Workbook_Open()
Dim sh as Worksheet
On error resume Next
set sh = ThisWorkbook.Worksheets(format(date,"yyyymmdd"))
On Error goto 0
if not sh is nothing then
sh.Activate
else
with thisworkbook
.worksheets(worksheets.count).Activate
End With
End if
End Sub

Chip Pearson's page on Event
http://www.cpearson.com/excel/events.htm

--
Regards,
Tom Ogilvy



"D" <valdezdj@fotf.org> wrote in message
news:D41B7B83-72C7-4296-B6B8-4A2B67BF1481@microsoft.com...
> Hello.
> I have a spreadsheet that has a list of worksheets that are named certain
> dates and I would like to create an event that when I open the file it
goes
> to that tab according to today's date. It seems fairly easy but I think
that
> I'm complicating the problem. Thanks for your help.
> --
> D

Much better code than mine and a better description. Use this code instead....

"Tom Ogilvy" wrote:

> go to the VBE and go to the Thisworkbook module of the workbook.
>
> in the left dropdown select workbook and in the right, select Open (top of
> the module dropdowns).
>
> change the formatting or logic to produce the date related name of the sheet
> you want to go to.
>
> Private Sub Workbook_Open()
> Dim sh as Worksheet
> On error resume Next
> set sh = ThisWorkbook.Worksheets(format(date,"yyyymmdd"))
> On Error goto 0
> if not sh is nothing then
> sh.Activate
> else
> with thisworkbook
> .worksheets(worksheets.count).Activate
> End With
> End if
> End Sub
>
> Chip Pearson's page on Event
> http://www.cpearson.com/excel/events.htm
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "D" <valdezdj@fotf.org> wrote in message
> news:D41B7B83-72C7-4296-B6B8-4A2B67BF1481@microsoft.com...
> > Hello.
> > I have a spreadsheet that has a list of worksheets that are named certain
> > dates and I would like to create an event that when I open the file it
> goes
> > to that tab according to today's date. It seems fairly easy but I think
> that
> > I'm complicating the problem. Thanks for your help.
> > --
> > D
>
>
>