Dates to Worksheets

[Guest]

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.

 

[Guest]

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

 

[Tom Ogilvy]

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

 

[Guest]

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

 

[Guest]

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

 

[Tom Ogilvy]

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