Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open to today's date
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" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open to today's date
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open to today's date
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" wrote in message ... 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open to today's date
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" wrote in message ... 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" wrote in message ... 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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open to today's date
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" wrote in message ... 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" wrote in message ... 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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open to today's date
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" wrote in message ... 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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open to today's date
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formatting for cell date to equal today's date | Excel Worksheet Functions | |||
date in Cell to change colors if the date is beyond today's date | Excel Discussion (Misc queries) | |||
How to use Today's date in a cell and make it stay the same date | Excel Discussion (Misc queries) | |||
Open file saved as today's date | Excel Programming | |||
on open goto cell with today's date | Excel Programming |