Add worksheet named as a date

[Jako]

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

 

[Tom Ogilvy]

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.

 

[JE McGimpsey]

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 before:

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