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.