does this help?
Code:
Sub test()
Dim ws As Worksheet, n As Integer
For Each ws In Sheets
Do Until WeekDay(Date + n) <> 1 Or WeekDay(Date + n) <> 7
n = n + 1
Loop
ws.Name = Format(Date + n,"dd.mm.yy") : n = n + 1
Next
End Sub
Hi,
There are a few problems with this but many thanks for the input. The code above always starts with the current date rather than a user specified date which means that the sheet must be prepared 'on the day' for it to label the sheets accurately. Often it is very hectic on the first day so it would be beneficial if the worksheet can be prepared in advance. Another problem is that it also includes dates that fall on Saturday & Sunday - as we never work weekends these sheets will never be used.
Maybe a better design idea on my part is needed. How about a Pop-up box that asks for date ranges? There are ALWAYS 6 half terms to a school year so how about a 6x2 pop up box / table that is formatted in the following way:
Title of input box: Input School Half Term Date Ranges
******** Start Date ** End Date **
Autumn 1
Autumn 2
Spring 1
Spring 2
Summer 1
Summer 2
Would the user have to also input the start day for each half term or would excel 'know that 1.10.06 is a Sunday?
If the macro could create worksheets for these dates and clone the first worksheet in the workbook then that would save me LOADS of time copying and pasting
Oh by the way it is my birthday today!
