The code underneath was kindly suggested by Jindon for formatting worksheet names as dates.
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
However, there were a few problems with this but many thanks for the input Jindon. 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. Is it possible to make a macro that creates a Pop-up box that asks for date ranges and generates copies of the first worksheet labelled as dates? 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 (remembering that saturdays and sundays must be excluded):
Title of input box: Input School Half Term Date Ranges
******** Start Date ** End Date **
Autumn 1 03.09.06 18.09.06
Autumn 2
Spring 1
Spring 2
Summer 1
Summer 2
The macro would then create a series of sheets from 03.09.06 to 18.10.06 as well as the other 5 date ranges (excluding weekends) using the first sheet as a template. Would the user have to also input the start day for each half term or would excel 'know that 04.09.06.06 is a Monday and 18.10.06 is a Wednesday?
If the macro could create worksheets for these date ranges and clone the first worksheet in the workbook then that would save me LOADS of time copying and pasting each year.
Oh by the way it is my birthday today!
Eek
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
However, there were a few problems with this but many thanks for the input Jindon. 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. Is it possible to make a macro that creates a Pop-up box that asks for date ranges and generates copies of the first worksheet labelled as dates? 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 (remembering that saturdays and sundays must be excluded):
Title of input box: Input School Half Term Date Ranges
******** Start Date ** End Date **
Autumn 1 03.09.06 18.09.06
Autumn 2
Spring 1
Spring 2
Summer 1
Summer 2
The macro would then create a series of sheets from 03.09.06 to 18.10.06 as well as the other 5 date ranges (excluding weekends) using the first sheet as a template. Would the user have to also input the start day for each half term or would excel 'know that 04.09.06.06 is a Monday and 18.10.06 is a Wednesday?
If the macro could create worksheets for these date ranges and clone the first worksheet in the workbook then that would save me LOADS of time copying and pasting each year.
Oh by the way it is my birthday today!
Eek
