Autoformat date as sheet names

[PP]

How would one go about doing this?

First sheet's date is inputted / renamed manually, eg (2.10.06) - UK format. Sheet dates start on Monday and go through to Friday - Saturday & Sunday dates are skipped. Macro autodates all sheets in workbook using first sheet as the 'start date'.

 

[stanleydgromjr]

Are there always five sheets in the workbook?

What are the default sheet names?

Is this workbook a template file with five sheets?


Have a great day,
Stan

 

[PP]

Are there always five sheets in the workbook?

No - the number of sheets in the workbook can vary considerably depending on the length of the term. Sometimes it can have 20 sheets, other times it might have 40.

What are the default sheet names?

Sheet 1, sheet 2 etc. Except that sheet 1 would be renamed to the date of the first day of term.

Is this workbook a template file with five sheets?
No - see first answer.


Have a great day,
Stan

You too!
:P

 

[PP]

I hope this is possible

 

[jindon]

ADVERTISEMENT

does this help?

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

 

[PP]

does this help?

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!

 

[PP]

Is this suggestion a better solution?