Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add worksheet named as a date
Could anyone please suggest a way to do this If today's day is a monday then i want to add a worksheet named wit fridays date and prefixed with "WE". Eg. If the workbook is opened on Monday 15th November 2004 then i want t add a worksheet named "WE 19.11.04". If the Monday is the last monday in the month i also want to add worksheet named "November 2004" or whatever the month is ie. If opened the workbook on the last monday in June then i want to create Worksheet named "June 2004". Also if the Monday is the second Monday in the month i then want t hide all worksheets named from the month before. Eg. If it is the second monday in November then i want to hide al worksheets with the 10th month in their name. So for example a worksheet "WE 22.10.04" would be hidden as would worksheet "WE 29.10.04" but a worksheet named "WE 05.11.04" would no be hidden. I do want to keep all worksheets with the month and yea visible though. So using the above, the worksheet "October 2004" would stay visible. Any advice appreciated -- Jak ----------------------------------------------------------------------- Jako's Profile: http://www.excelforum.com/member.php...nfo&userid=871 View this thread: http://www.excelforum.com/showthread.php?threadid=27759 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add worksheet named as a date
If you know how to write VBA, then you should be able to create a macro
which contains all the logic and rules you have described. what kind of advice were you looking for. Perhaps if you post (in your post, not as an attachment) what you have written so far and indicate where you are having problems, someone will have specific advice for you. -- Regards, Tom Ogilvy "Jako" wrote in message ... Could anyone please suggest a way to do this If today's day is a monday then i want to add a worksheet named with fridays date and prefixed with "WE". Eg. If the workbook is opened on Monday 15th November 2004 then i want to add a worksheet named "WE 19.11.04". If the Monday is the last monday in the month i also want to add a worksheet named "November 2004" or whatever the month is ie. If i opened the workbook on the last monday in June then i want to create a Worksheet named "June 2004". Also if the Monday is the second Monday in the month i then want to hide all worksheets named from the month before. Eg. If it is the second monday in November then i want to hide all worksheets with the 10th month in their name. So for example a worksheet "WE 22.10.04" would be hidden as would a worksheet "WE 29.10.04" but a worksheet named "WE 05.11.04" would not be hidden. I do want to keep all worksheets with the month and year visible though. So using the above, the worksheet "October 2004" would stay visible. Any advice appreciated. -- Jako ------------------------------------------------------------------------ Jako's Profile: http://www.excelforum.com/member.php...fo&userid=8710 View this thread: http://www.excelforum.com/showthread...hreadid=277597 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add worksheet named as a date
Any advice appreciated.
My first piece of advice is not to do it this way (i.e., add the sheet if the workbook is opened on a Monday). What happens if Monday is a holiday and you open the sheet on Tuesday, instead? Rather, I'd suggest a macro to add a sheet for the previous week (actually, if I were designing it, I'd probably base the worksheet name on the last worksheet in the workbook, but i don't know if that would work for you). This is a modification of something I've done befo Public Sub AddSheets() Dim wsSheet As Worksheet Dim dtMonday As Date Dim sFriday As String 'Get Monday of current week dtMonday = Date - WeekDay(Date, vbMonday) + 1 sFriday = Format(dtMonday + 4, "\W\E dd.mm.yy") 'Check if Friday wksht exists. Exit if so, create if not With Worksheets On Error Resume Next Set wsSheet = .Item(sFriday) On Error GoTo 0 If Not wsSheet Is Nothing Then MsgBox "Sheet " & sFriday & " already exists." Else .Add(After:=.Item(.Count)).Name = sFriday 'Add Month If Month(dtMonday + 7) < Month(dtMonday) Then .Add(After:=.Item(.Count)).Name = _ Format(dtMonday, "mmmm yyyy") 'or hide previous ElseIf (Day(dtMonday) 6) And (Day(dtMonday) < 15) Then For Each wsSheet In Worksheets If InStr(wsSheet.Name, "." & Month(dtMonday - _ 28) & ".") Then wsSheet.Visible = False Next wsSheet End If End If End With End Sub In article , Jako wrote: Could anyone please suggest a way to do this If today's day is a monday then i want to add a worksheet named with fridays date and prefixed with "WE". Eg. If the workbook is opened on Monday 15th November 2004 then i want to add a worksheet named "WE 19.11.04". If the Monday is the last monday in the month i also want to add a worksheet named "November 2004" or whatever the month is ie. If i opened the workbook on the last monday in June then i want to create a Worksheet named "June 2004". Also if the Monday is the second Monday in the month i then want to hide all worksheets named from the month before. Eg. If it is the second monday in November then i want to hide all worksheets with the 10th month in their name. So for example a worksheet "WE 22.10.04" would be hidden as would a worksheet "WE 29.10.04" but a worksheet named "WE 05.11.04" would not be hidden. I do want to keep all worksheets with the month and year visible though. So using the above, the worksheet "October 2004" would stay visible. Any advice appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum of named ranges conditional to date? | Excel Worksheet Functions | |||
Do not specify worksheet in a named range | Excel Discussion (Misc queries) | |||
Getting a named range to appear in another worksheet | Excel Discussion (Misc queries) | |||
Link title of worksheet to whatever the worksheet tab is named | Excel Discussion (Misc queries) | |||
named range not specific to worksheet | Excel Programming |